How to Extract Rows from Comma-Separated Column Values in SQL?

Extracting Rows from Comma-Separated Column Values

Problem:

Given a table containing a column with comma-separated values, the objective is to convert each value into separate rows.

Query:

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);

Explanation:

1. The inner query creates a new column named String that wraps the original comma-separated values within XML tags. This is accomplished using the CAST and REPLACE functions to convert the values into a well-formed XML string.
2. The outer query performs a cross apply to parse the XML string into nodes using the nodes function. Each node represents a single comma-separated value.
3. The a.value('.','VARCHAR(100)') expression extracts the value of the a node as a VARCHAR(100) data type. This gives us the individual processed rows.

Reference:

• [Converting Single Comma Separated Row Values to Multiple Rows using SQL XML](http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html)

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