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:
