Concatenate values based on ID

Question

I Have a table called Results and the data looks like:

Response_ID    Label
12147          It was not clear
12458          Did not Undersstand
12458          Was not resolved
12458          Did not communicate
12586          Spoke too fast
12587          Too slow

Now I want the ouput to display one row per ID and the values from Label to be concatenated and seperated by comma

My Output should look like:

Response_ID    Label
12147          It was not clear
12458          Did not Undersstand,Was not resolved,Did not communicate
12586          Spoke too fast
12587          Too Slow

How can I do this:

Answer 1

Consider this, it is very performant:

http://jerrytech.blogspot.com/2010/04/tsql-concatenate-strings-1-2-3-and.html

Avoid XML functions because they are not performant.

This will take some effort to implement, but millions of rows => milliseconds to run.

Answer 2

Check the link below, it approaches your problem with many different solutions

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Answer 3

DECLARE @Results TABLE(Response_ID INT, Label VARCHAR(80));

INSERT @Results(Response_ID, Label)
SELECT 12147,'It was not clear'
UNION SELECT 12458,'Did not Undersstand'
UNION SELECT 12458,'Was not resolved'
UNION SELECT 12458,'Did not communicate'
UNION SELECT 12586,'Spoke too fast'
UNION SELECT 12587,'Too slow';

WITH x AS 
(
  SELECT Response_ID FROM @Results 
  GROUP BY Response_ID
)
SELECT x.Response_ID, Label = STUFF((SELECT ',' + Label
    FROM @Results WHERE Response_ID = x.Response_ID
    FOR XML PATH('')), 1, 1, '')
    FROM x;

Answer 4

You can not be sure about the order of the strings concatenated without an order by statement in the sub query. The .value('.', 'varchar(max)') part is there to handle the case where Label contains XML-unfriendly characters like &.

declare @T table(Response_ID int, Label varchar(50))
insert into @T values
(12147,          'It was not clear'),
(12458,          'Did not Undersstand'),
(12458,          'Was not resolved'),
(12458,          'Did not communicate'),
(12586,          'Spoke too fast'),
(12587,          'Too slow')

select T1.Response_ID,
       stuff((select ','+T2.Label
              from @T as T2
              where T1.Response_ID = T2.Response_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as Label
from @T as T1
group by T1.Response_ID