Multiple rows in single field separated by commas SQL Server

Question

Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?

What will be the SQL Server code to achieve this result:

SO number  Line number            
123           6
123          189
456           34

Output required:

SO number  Line number 
 123            6,189
 456               34

This question is so incredibly duplicate. Googling for "SQL Server concat aggregate" must return an infinite amount of results. — usr, Jul 29 '12 at 16:33
@user1561175: Yes, `GROUP_CONCAT` is a MySQL function. SQL Server doesn't have a direct equivalent. That's why the other question was about *simulating* that function in SQL Server. — Andriy M, Jul 30 '12 at 09:45

praveen · Answer 1 · 2012-07-30T00:46:33.447

Try this :-

CREATE TABLE #Sample ([SO Number] INT, [Line Number] INT)

INSERT INTO #Sample ([SO Number],[Line Number]) VALUES (123,6)
INSERT INTO #Sample ([SO Number],[Line Number]) VALUES (123,189)
INSERT INTO #Sample ([SO Number],[Line Number]) VALUES (456,34)


SELECT ID,
STUFF((
SELECT ', ' + cast([Line Number] as varchar(max) )
FROM #Sample 
WHERE ([SO Number] = a.[SO Number]) 
FOR XML PATH (''))
,1,2,'') AS b
FROM #Sample a
GROUP BY [SO Number]

Edit :

XML PATH (''):Is used to retrieve the column values seperated by comma 
Ex:- ,6,189,34

STUFF: Is used to insert a string into another string .The expression is

STUFF ( character_expression , start , length ,character_expression )

Start length is 1 since we have to replace the first comma created by XML PATH . Length is 1 for the space '' which is being inserted

So even if you have 20K rows the expression will remain same as 1,2 is used to insert space

i think its a silly doubt.but i am not that great with sql.my actual data has more than 20,000 rows.how would i write 1,2,'' in that case. — user1561175, Jul 29 '12 at 17:24

score 0 · Answer 2 · answered Jul 30 '12 at 05:36

DECLARE @Sample Table([SO Number] INT, [Line Number] INT)

INSERT INTO @Sample ([SO Number],[Line Number]) VALUES (123,6)
INSERT INTO @Sample ([SO Number],[Line Number]) VALUES (123,189)
INSERT INTO @Sample ([SO Number],[Line Number]) VALUES (456,34)

select * from @Sample

select [SO Number],STUFF((select ','+cast([Line Number] as varchar(100)) from  @Sample s1 where s1.[SO Number] =s.[SO Number]  FOR XML path('')),1,1,'' )  as [Line Number]     from  @Sample s
group by [SO Number]

Multiple rows in single field separated by commas SQL Server

2 Answers2