[转]用 SQL 语句对一行数据拆分成多行的方法

有时候我们也许对一行数据拆分成多行的操作，例如：

Col1        COl2
----------- ------------
 1           a,b,c
2           d,e
3           f

拆分成

 Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f

 下面给出几个经常用到的方法：

 SQL2000用辅助表:

if object_id ( ' Tempdb..#Num ' ) is not null
drop table #Num
go
select top 100 ID = Identity ( int , 1 , 1 ) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2 = substring (a.Col2,b.ID, charindex ( ' , ' ,a.Col2 + ' , ' ,b.ID) - b.ID)
from
Tab a,#Num b
where
charindex ( ' , ' , ' , ' + a.Col2,b.ID) = b.ID -- 也可用 substring(','+a.COl2,b.ID,1)=','

SQL2005用Xml:

select
a.COl1,b.Col2
from
( select Col1,COl2 = convert (xml, ' <root><v> ' + replace (COl2, ' , ' , ' </v><v> ' ) + ' </v></root> ' ) from Tab)a
outer apply
( select Col2 = C.v.value( ' . ' , ' nvarchar(100) ' ) from a.COl2.nodes( ' /root/v ' )C(v))b

SQL05用CTE:

; with roy as
( select Col1,COl2 = cast ( left (Col2, charindex ( ' , ' ,Col2 + ' , ' ) - 1 ) as nvarchar ( 100 )),Split = cast ( stuff (COl2 + ' , ' , 1 , charindex ( ' , ' ,Col2 + ' , ' ), '' ) as nvarchar ( 100 )) from Tab
union all
select Col1,COl2 = cast ( left (Split, charindex ( ' , ' ,Split) - 1 ) as nvarchar ( 100 )),Split = cast ( stuff (Split, 1 , charindex ( ' , ' ,Split), '' ) as nvarchar ( 100 )) from Roy where split > ''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
转自：http://www.cqun.com/2009/04/sql.html

转载于:https://www.cnblogs.com/acis_/archive/2009/08/14/1545990.html