根据ID连接值

Question

我有一个名为“结果”的表，数据如下：Response_ID    Label12147          It was not clear12458          Did not Undersstand12458          Was not resolved12458          Did not communicate12586          Spoke too fast12587          Too slow现在，我希望输出每个ID显示一行，并将Label中的值连接起来并用逗号分隔我的输出应如下所示：Response_ID    Label12147          It was not clear12458          Did not Undersstand,Was not resolved,Did not communicate12586          Spoke too fast12587          Too Slow我怎样才能做到这一点：

Answer 1

您不能确定在子查询中没有order by语句的情况下串联的字符串的顺序。该.value('.', 'varchar(max)')部分用于处理Label包含XML不友好字符（如）的情况&。

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

Answer 2

DECLARE @Results TABLE(Response_ID INT, Label VARCHAR(80));

INSERT @Results(Response_ID, Label)

SELECT 12147,'It was not clear'

UNION SELECT 12458,'Did not Undersstand'

UNION SELECT 12458,'Was not resolved'

UNION SELECT 12458,'Did not communicate'

UNION SELECT 12586,'Spoke too fast'

UNION SELECT 12587,'Too slow';

WITH x AS 

(

  SELECT Response_ID FROM @Results 

  GROUP BY Response_ID

)

SELECT x.Response_ID, Label = STUFF((SELECT ',' + Label

    FROM @Results WHERE Response_ID = x.Response_ID

    FOR XML PATH('')), 1, 1, '')

    FROM x;