How Can I Concatenate Labels Based on Matching IDs in SQL?

Concatenating Values Based on ID

To solve the provided task of combining multiple rows with the same ID and concatenating the corresponding labels, utilize the following SQL query:

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

Within the query:

• @T represents the input table with the Response_ID and Label columns.
• Data is inserted into @T to serve as the starting point for the query.
• The first subquery retrieves the Label values for each Response_ID and concatenates them with commas. The FOR XML clause generates an XML string, which is then converted to a single string using the VALUE('','VARCHAR(MAX)') expression.
• The outer query groups the results by Response_ID and uses STUFF() to concatenate the combined Label values with the Response_ID.
• Finally, it displays the concatenated values for each unique Response_ID.

The above is the detailed content of How Can I Concatenate Labels Based on Matching IDs in SQL?. For more information, please follow other related articles on the PHP Chinese website!