How Can I Split Comma-Separated Values into Rows in SQL Server?

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:

1. Convert the string to an XML fragment: Wrap the comma-separated string with XML tags to transform it into an XML fragment. This operation adds opening and closing tags to each value, creating a hierarchal XML structure.
2. Use CROSS APPLY to split the XML fragment: Use the CROSS APPLY operator to split the XML fragment into individual nodes. Each node represents a single value in the original string.
3. Extract the value from the node: Apply the VALUE() method to each node to extract the actual value as a string. This converts the XML node back into a usable data type.
4. Combine the id and extracted values: Join the extracted values with the original table's id column to create the new table. This ensures that each value is associated with its corresponding row in the original table.

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!