user2961127 user2961127 - 1 year ago 67
SQL Question

Displaying column values into row

I have a table which has following values:

case code id
100 A10 1
100 A11 2
100 A12 3
101 A11 4
102 A10 5

I need to write a query to get the following output:

case code
100 A10, A11, A12
101 A11
102 A10

Any help would be appreciated. Thanks.

Answer Source

Using STUFF you can get the expected result

DECLARE @Test Table([case] int, [code] varchar(20))
INSERT @Test([case],[code])
(100,'A10'), (100,'A11'),
(100,'A12'), (101,'A11'), (102, 'A10')

STUFF((SELECT ', '+ code
       FROM @Test T1
       WHERE T1.[case] = T2.[case]
       FOR XML PATH('')), 1, 1,''
     ) AS code 
FROM @Test T2