How to Convert Comma-Separated Values to Rows in SQL Server?

Convert Comma Separated Values to Row-Structured Data

In SQL Server, when data is stored in a comma-separated format within a column, it can be challenging to extract and manipulate individual values. This article demonstrates how to convert such data into a row-structured format using a select query.

Consider a table named Sample with a column called String containing comma-separated values, such as:

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

To extract and present the individual values as rows, we can employ the following steps:

1. Wrap the String column within XML tags:

   CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML)

   This creates an XML fragment where each value is encapsulated within an tag.

2. Use the CROSS APPLY operator to perform a hierarchical query:

   CROSS APPLY String.nodes ('/M') AS Split(a)

   This cross-applies a nodes method on the XML fragment, resulting in a new row for each tag, which contains the individual values.

3. Combine the Id column with the Split(a).value('text()') expression:

   SELECT A.[id], Split.a.value('.','VARCHAR(100)') AS String
   FROM (SELECT [id], CAST('...AS XML) AS String FROM Sample) AS A

   This matches the Id with the extracted values and returns them as separate rows.

The result will resemble the desired output:

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

This technique enables the extraction and presentation of comma-separated values as rows, allowing for efficient analysis and manipulation of the data.

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