How to Concatenate Multiple Labels into a Single Row Based on ID?

Concatenating Values Based on ID

This question seeks to transform a table with multiple rows per ID and a single 'Label' column into a table with one row per ID and a concatenated 'Label' column. Each label should be separated by commas.

The provided data consists of the following observations:

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

The desired output is:

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

To achieve this transformation, the following code snippet can be used:

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

The subquery in the 'stuff' function uses concatenation via the ' ' operator and XML methods to handle the combination of labels. The 'type' parameter's 'value' function converts the XML output into a varchar(max) type to ensure proper display of concatenated results.

Please note that without an order by statement in the subquery, the order of concatenated strings cannot be guaranteed.

The above is the detailed content of How to Concatenate Multiple Labels into a Single Row Based on ID?. For more information, please follow other related articles on the PHP Chinese website!