Merge second column in a table based on row ID SQL

Question

I'm looking to write a database script that will go through a table and will give me one comma separated output.

I need to do this all in a one off script. I was thinking about starting with a temp table of just the IDs and then getting all of the items but I'm not really sure how to "foreach" a database.

What I have                    What I'm looking for

 ID | Items                       ID | Items
 1  |  A                          1  | A,B,C,D
 1  |  B                          2  | A,B,C
 1  |  C                          3  | B,C
 1  |  D
 2  |  A
 2  |  B
 2  |  C
 3  |  B 
 3  |  C

This has been asked many many times before, here is just one possible asnwer: [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — Lamak, Apr 20 '12 at 19:32

score 0 · Accepted Answer · answered Apr 20 '12 at 19:28

0

declare @t table(id int, items varchar(8));
insert into @t values(1,'A')
, (1,'B')
, (1,'C')
, (1,'D')
, (2,'A')
, (2,'B')
, (2,'C')
, (3,'B')
, (3,'C')

select distinct id
, (STUFF((SELECT ',' + items FROM @t t2 WHERE  t1.id = t2.id ORDER BY items
FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as items
from @t t1

Results:

id          items
----------- ------------------------
1           A,B,C,D
2           A,B,C
3           B,C

answered Apr 20 '12 at 19:28

John Dewey

6,985
3
22
26

This looks perfect, but would you mind explaining what it actually does? I've never seen the STUFF keyword before... – Jabsy Apr 20 '12 at 19:31
It just takes advantage of the fact that XML results naturally aggregate child tags under each parent, where it can then be retrieved as a single string by the .value XML function. It might help to also look at the actual XML: `SELECT id,',' + items FROM @t t2 FOR XML PATH('')`. – John Dewey Apr 20 '12 at 20:25
`STUFF` is a function to insert a string into another string. Here it is used to simply strip the leading `,` from the result, by replacing the first position with an empty string. – John Dewey Apr 20 '12 at 20:27

score 0 · Answer 2 · answered Apr 20 '12 at 20:28

Could be my imagination but this seems to run a bit faster and the query plan is a bit easier to read. However, the xmlreader in query plans always seems to over estimate actual query cost.

declare @table table (
id int
,val varchar(2)
)
insert @table
Values (1 ,'a'),
(1,'b'),
(1,'c'),
(1,'d'),
(2,'a'),
(2,'b'),
(2,'c'),
(3,'a'),
(3,'b')
select id, stuff((
select (','+val) from @table q1
    where q1.id = q2.id
    for xml path('')),1,1,'') val
    from @table q2
    group by q2.id

This almost does the same thing except it excludes the data reader for the xml by making an improper xml document with no elements specifications. As for the stuff built in function see http://msdn.microsoft.com/en-us/library/ms188043.aspx He is using stuff to manipulate the string. If you add commas you either end up with a string that has an extra comma at the end or one at the begining. This example replaces the first 2 characters in the string starting at position 1 with the blank string''

select STUFF(',,test,test1,test2,test3',1,2,'')

Data:

test,test1,test2,test3

Merge second column in a table based on row ID SQL

2 Answers2