10

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:

4 Answers4

10

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Good point about .value, I always make assumptions about data in the question being broadly representative. It doesn't cause an error, of course, just displays the entity (e.g. &). However I am not so sure order was important to the OP - and what order by would you add, if it were? We'd need more information. – Aaron Bertrand Jul 06 '11 at 22:31
  • @Aaron – Yes, we need more info about the order. I was just making a point to @Sam that even though this sample code concatenates the string in the expected order, there is no guarantee that it will do so every time. – Mikael Eriksson Jul 06 '11 at 22:34
  • THis is not a good solution because it is a performance problem when there are many rows in the table. XML functions are slow. – Jerry Nixon Jul 06 '11 at 23:52
1

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/

niktrs
  • 9,858
  • 1
  • 30
  • 30
1

Given this sample data:

CREATE TABLE #Results(Response_ID int, Label varchar(80));

INSERT #Results(Response_ID, Label) 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');

On older versions you can use FOR XML PATH for (grouped) string aggregation:

SELECT r.Response_ID, Label = STUFF((SELECT ',' + Label
    FROM #Results WHERE Response_ID = r.Response_ID
    FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '')
  FROM #Results AS r
  GROUP BY r.Response_ID;

If you are on SQL Server 2017 or greater, the query is much simpler:

SELECT r.Response_ID, Label = STRING_AGG(Label, ',')
  FROM #Results AS r
  GROUP BY r.Response_ID;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
-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.

Jerry Nixon
  • 31,313
  • 14
  • 117
  • 233
  • @jerry-nixon-msft , I didn't downvote you, but I did see some discussion about the performance of a recursive CTE vs For XML here: http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings It appears that For XML is faster on large datasets. – devinbost May 06 '17 at 03:41