根据 ID 连接值 [英] Concatenate values based on 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 连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！