SQL Server – Turning a Comma Separated string into individual rows

Let’s say you have a column in a table that is comma delimited. The value in the column is  ‘APPLE, ORANGE, GRAPE’. You want to break down this string value into individual rows. You may also want to create an auto-incremented IDENTITY column too. Here is one way it can be done.

DECLARE @TempTable TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, comma_del_string varchar(MAX))
INSERT INTO @TempTable
SELECT 'APPLE, ORANGE, GRAPE'
select * from @TempTable

DECLARE @S varchar(max)
DECLARE @Split char(1) = ','
DECLARE @X xml

SELECT @S = (select comma_del_string from @TempTable where SEQ_NO = 1)
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

DECLARE @userData TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, answer varchar(MAX))
INSERT INTO @userData
SELECT [Value] = T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c)

select * from @userData

Here is the output:

image

 

Leave a Reply

Your email address will not be published. Required fields are marked *