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.
精彩评论