How to Concatenate Multiple Rows into a Single String in SQL?

Concatenating Values Based on ID

In data manipulation, it's often necessary to combine data from multiple rows into a single string. This can be achieved through concatenation, where a string is assembled from smaller fragments.

Problem Statement:

You have a table called "Results" with two columns: "Response_ID" and "Label." Each "Response_ID" corresponds to multiple "Label" values. Your goal is to generate a new table with one row per "Response_ID" and all the "Label" values concatenated into a single string, separated by commas.

Solution:

To concatenate values based on "Response_ID," you can use the following SQL query:

select T1.Response_ID,
       stuff((select ','+T2.Label
              from Results as T2
              where T1.Response_ID = T2.Response_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as Label
from Results as T1
group by T1.Response_ID

Explanation:

• T1.Response_ID: The "Response_ID" column from the original table.

• Stuff(): This function concatenates strings. It takes the following arguments:

  • The resulting string to be concatenated (in parentheses).
  • The starting position in the resulting string where concatenation should begin (1 in this case).
  • The length of the substring to be removed from the beginning of the resulting string (1 in this case).
  • The string to be concatenated.
• XML Path: This subquery generates an XML fragment for the concatenation process. Each "Label" value is wrapped in a comma-separated XML element.
• value(): This function extracts the concatenated string from the XML fragment.

Example:

Consider the following table:

Response_ID	Label
12147	It was not clear
12458	Did not Understand
12458	Was not resolved
12458	Did not communicate
12586	Spoke too fast
12587	Too slow

The query above would produce the following output:

Response_ID	Label
12147	It was not clear
12458	Did not Understand,Was not resolved,Did not communicate
12586	Spoke too fast
12587	Too slow

Note: The order of the concatenated strings may not always be predictable. For precise control over the order, you can use an "ORDER BY" statement in the subquery.

The above is the detailed content of How to Concatenate Multiple Rows into a Single String in SQL?. For more information, please follow other related articles on the PHP Chinese website!