用 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)') froma.COl2.nodes('/root/v')C(v))b

SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) asnvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') asnvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) asnvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') asnvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

转自：http://www.cqun.com/2009/04/sql.html