How to Concatenate Multiple SQL Values Based on a Common Identifier?

Concatenate Multiple Values Based on a Common Identifier Using SQL

Suppose you have a table with two columns: Response_ID and Label, where multiple Label values can be associated with each Response_ID. You may encounter the need to concatenate all the Label values for each unique Response_ID.

This can be achieved using the following SQL statement:

-- Sample data
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')

-- Query to concatenate Label values
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

Explanation:

• The subquery (select ',' T2.Label ...) concatenates the Label values for each Response_ID into a single string, separated by commas. The for xml path(''), type clause converts the concatenated string into an XML fragment.
• The .value('.', 'varchar(max)') part handles cases where the Label contains characters that could cause XML parsing errors, such as &.
• The stuff(...) function removes the leading comma from the concatenated string.
• Finally, the group by clause aggregates the concatenated Label values for each unique Response_ID.

The result will be a table with one row per Response_ID, and the concatenated Label values separated by commas.

The above is the detailed content of How to Concatenate Multiple SQL Values Based on a Common Identifier?. For more information, please follow other related articles on the PHP Chinese website!