Query to Concatenate Many to One

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to Concatenate Many to One

Author

Topic

markfigel
Starting Member

8 Posts

Posted - 2008-08-28 : 15:21:03

Hi,

I'm trying to create a query that will result in distinct values in column 1 and concatenate all of the possible column 2 values where there is a many to one relationship. Any ideas?

Table: POLIST

PONum ReqNum
PO1010 1190
PO1010 1191
PO1011 1192
PO1012 1193
PO1013 1194
5 records

Query Result

PONum ReqNum
PO1010 1190, 1191
PO1011 1192
PO1012 1193
PO1013 1194
4 records

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 15:27:31

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 15:29:26

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
is also helpful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 01:46:15

if its sql 2005, you can do like this

SELECT t.PONum,LEFT(nl.numlist,LEN(nl.numlist)-1)
FROM(SELECT DISTINCT PONum FROM POList) t
CROSS APPLY(SELECT CAST(ReqNum AS varchar(10)) + ',' AS [text()]
            FROM POList
            WHERE PONum=t.PONum
            FOR XML PATH(''))nl(numlist)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 02:39:54

or
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources