开发者

Concatenate values based on ID

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 开发者_Go百科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:


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


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/


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;


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜