Splitting Comma-Separated Values into Rows
In SQL Server, converting a comma-separated string stored in a table column into multiple rows can be achieved using a combination of XML and string manipulation techniques. The objective is to convert a table containing a single row with a comma-separated string into a table with multiple rows, each containing a unique value from the original string.
To accomplish this, follow these steps:
Here's an example query that demonstrates this process:
SELECT A.[id], Split.a.value('.', 'VARCHAR(100)') AS String FROM (SELECT [id], CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String FROM TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
This query will produce the desired output, where each row in the original table is expanded into multiple rows, each containing a single value from the comma-separated string.
The above is the detailed content of How Can I Split Comma-Separated Values into Rows in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!