Displaying column values into row

Question

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.

Take a look at this question and answers - http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Alexei - check Codidact, May 04 '16 at 18:01
There is a good post for this questin [here](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Siyavash Hamdi, May 04 '16 at 18:07

score -1 · Accepted Answer · answered May 04 '16 at 18:02

-1

Using STUFF you can get the expected result

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

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

answered May 04 '16 at 18:02

Arulkumar

12,966
14
47
68

Uncredited duplicate of the technique in this answer: http://stackoverflow.com/a/5981860/1507566 – Tab Alleman May 04 '16 at 18:30
@TabAlleman: Using STUFF with FOR XML PATH is the usual pattern in string concatenation to create comma separated formats. More over I used my [old answer](http://stackoverflow.com/a/33955969/2451726) only. Disagree with your down-vote. – Arulkumar May 04 '16 at 18:38
2

Rather than answer questions that have already been asked and answered on this site, we should be directing new askers to the duplicate question, rather than cluttering up the site with more duplicates. That you yourself had already answered this question and duplicated your own answer only strengthens my point. – Tab Alleman May 04 '16 at 18:48
1

@TabAlleman - But does not deserve downvote – Pரதீப் May 05 '16 at 01:29
@Prdp there are those who agree with you and those who agree with me: http://meta.stackoverflow.com/questions/322096/should-we-try-to-train-users-to-close-as-duplicate-vs-answer – Tab Alleman May 05 '16 at 12:28

Displaying column values into row

1 Answers1