How to Split Comma-Separated Values into Multiple Rows in SQL Server?

Splitting Comma-Separated Values into Table Rows

Data stored with multiple values separated by commas can pose challenges when retrieving it in a structured format. This article aims to address this issue by providing a solution to convert comma-separated values in a SQL Server column into separate rows.

In the example provided, the Sample table contains a column named String with data like "abc,def,ghi" and "jkl,mno,pqr." The goal is to transform this data into a table with multiple rows, where each row contains a single value from the String column.

The following SQL Server query can achieve this transformation:

SELECT A.[id], Split.a.value('.', 'VARCHAR(100)') AS processedrows
FROM (SELECT [id], CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String FROM Sample) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

This query employs some advanced techniques to convert the comma-separated values into XML and then extract each value as a separate row. Refer to http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html for further details on this approach.

The above is the detailed content of How to Split Comma-Separated Values into Multiple Rows in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!