已知表格为
id name
1 jame，job
2 carl，mc
3 paul
拆分为
id name
1 jame
2 job
3 carl
4 mc
5 paul
----------------------------------------------------------------
--使用循环截取法
declare@tbtable(id intidentity(1,1),name char(50))
insert@tbselect'jame,job'
insert@tbselect'carl,mc'
insert@tbselect'paul'
declare c1 cursorforselect name from@tb
declare@tmptable(id intidentity(1,1),name char(50))
declare@schar(50)
open c1
fetch c1 into@s
while(@@fetch_status=0)
begin
whilecharindex(',',@s)>0
begin
insertinto@tmp
values(left(@s,charindex(',',@s)-1))
set@s=stuff(@s,1,charindex(',',@s),'')
end
INSERT@tmpVALUES(@s)
fetch nextfrom c1 into@s
end
select*from@tmp
close c1
deallocate c1
----------------------------------------------------------------
--使用动态SQL语句
declare@tbtable(id intidentity(1,1),name char(50))
insert@tbselect'jame,job'
insert@tbselect'carl,mc'
insert@tbselect'paul'
declare c1 cursorforselect name from@tb
declare@tmptable(id intidentity(1,1),name char(50))
declare@schar(50)
declare@sqlchar(8000)
open c1
fetch c1 into@s
while(@@fetch_status=0)
begin
set@sql='select '''+replace(@s,',',''' union all select ''')+''''
insertinto tmp --tmp只能是基本表，必须先定义好tmp的结构
exec(@sql)
fetch nextfrom c1 into@s
end
select*from@tmp
close c1
deallocate c1
--> 测试数据: #T
ifobject_id('tempdb.dbo.#T') isnotnulldroptable #T
createtable #T (id int,name varchar(8))
insertinto #T
select1,'jame,job'unionall
select2,'paul,mc'unionall
select3,'carl';
--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
select id,charindex(',',','+name),charindex(',',name+',')+1from #T
unionall
select a.id,b.P2,charindex(',',name+',',b.P2)+1from #T a join T b on a.id=b.id wherecharindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 -1) from #T a join T b on a.id=b.id orderby1
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/
--> 测试数据: #T
ifobject_id('tempdb.dbo.#T') isnotnulldroptable #T
createtable #T (id int,name varchar(8))
insertinto #T
select1,'jame,job'unionall
select2,'paul,mc'unionall
select3,'carl';
--> 2. 临时表法：速度比CTE方法相差无几
ifobject_id('tempdb.dbo.#') isnotnulldroptable #
selecttop8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a innerjoin # b onsubstring(','+a.name,b.id,1)=','
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/
3. XML法：速度较慢
select
a.id,b.name
from
(select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
(select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id name
----------- ---------
1 jame
1 job
2 mc
2 paul
3 carl
*/