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)
Tuesday, February 2, 2010
Convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server
Labels:
DotNetVJ,
SQL Server,
Vijaya Kadiyala
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment