How to Convert Comma-Separated Cell Values into Individual Rows Using SQL Server?

Converting Comma-Separated Cell Values into Individual Rows Using SQL Server

When dealing with data in a table, sometimes you may encounter values stored as comma-separated strings within a single cell. To facilitate analysis or further processing, it may be necessary to split these values into separate rows. This article explores how to achieve this objective using a SQL Server SELECT query.

Problem:

Consider a table named "Sample" with a column called "String" that contains comma-separated values. You need to transform the data into a format where each value occupies its own row:

Original table:

Id	String
1	abc,def,ghi
2	jkl,mno,pqr

Desired output:

Id	processedrows
1	abc
1	def
1	ghi
2	jkl
2	mno
2	pqr

Solution:

To achieve the desired output, you can utilize the following SELECT query:

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

Explanation:

1. The inner query, enclosed within parentheses, constructs an XML string from the "String" column by wrapping each value with XML tags (). This step is necessary to enable the subsequent splitting operation.
2. The CROSS APPLY operator applies the String.nodes() function to each row in the inner query, effectively splitting the "String" column into individual nodes within an XML tree.
3. Finally, the SELECT statement retrieves the "id" column along with the value of each split node, which is automatically converted to a VARCHAR data type.

Reference:

For more information on this technique and alternative solutions, refer to the following resource:

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

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