Pages

Tuesday, February 2, 2010

Convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server

Today, I would like to give you simple tip to convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server.
declare @String varchar(MAX)

set @String = '100,200,250,300,350,450,500'
set @String =','+ @String + ','
SELECT REPLACE(Val_Column,',','')
FROM
(
select
substring(@String,number,CHARINDEX(',',@String,number+1)-number) As Val_Column
from
master..spt_values
where number < LEN(@String)
and type = 'P'
) C
where Val_Column like ',%'

Output

--------------------------------------
100
200
250
300
350
450
500

(7 row(s) affected)

No comments: