本文介绍了根据 ID 连接值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为 Results 的表,数据如下所示:
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
现在我希望输出为每个 ID 显示一行,并将来自 Label 的值连接起来并用逗号分隔
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
我该怎么做:
推荐答案
您无法确定子查询中没有 order by 语句的字符串连接顺序..value('.', 'varchar(max)')
部分用于处理 Label
包含 XML-unfriendly 字符的情况像&
.
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
这篇关于根据 ID 连接值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!