用 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