--分离
select a.id,a.name,detail=substring(detail,b.id,charindex(',',detail+',',b.id)-b.id)
from 表 a join(
select id=a.id+b.id+c.id+d.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 500
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 5000
union all select id=6000 union all select 7000
) d
)b on substring(','+detail,b.id,1)=','
select a.id,a.name,detail=substring(detail,b.id,charindex(',',detail+',',b.id)-b.id)
from 表 a join(
select id=a.id+b.id+c.id+d.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 500
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 5000
union all select id=6000 union all select 7000
) d
)b on substring(','+detail,b.id,1)=','
create table 表(id int,name varchar(10),detail varchar(100))
insert 表 select 1,'张山','yyy,ddd,fff,....'
union all select 2,'李四','www,zzz,...'
union all select 3,'王五','qqq,....'
go--分离
select a.id,a.name,detail=substring(detail,b.id,charindex(',',detail+',',b.id)-b.id)
from 表 a join(
select id=a.id+b.id+c.id+d.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 500
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 5000
union all select id=6000 union all select 7000
) d
)b on substring(','+detail,b.id,1)=','
order by a.id,b.id
go--删除测试
drop table 表/*--测试结果id name detail
----------- ---------- ------------
1 张山 yyy
1 张山 ddd
1 张山 fff
1 张山 ....
2 李四 www
2 李四 zzz
2 李四 ...
3 王五 qqq
3 王五 ....(所影响的行数为 9 行)
--*/
select top 8000 id=identity(int,1,1) into #t from syscolumns a,syscolumns b
select a.id,a.name,detail=substring(detail,b.id,charindex(',',detail+',',b.id)-b.id)
from 表 a join #t b on substring(','+detail,b.id,1)=','
order by a.id,b.iddrop table #t
create table 表(id int,name varchar(10),detail varchar(100))
insert 表 select 1,'张山','yyy,ddd,fff,....'
union all select 2,'李四','www,zzz,...'
union all select 3,'王五','qqq,....'
go--用临时表的方法
select top 8000 id=identity(int,1,1) into #t from syscolumns a,syscolumns b
select a.id,a.name,detail=substring(detail,b.id,charindex(',',detail+',',b.id)-b.id)
from 表 a join #t b on substring(','+detail,b.id,1)=','
order by a.id,b.iddrop table #t
go--删除测试
drop table 表/*--测试结果id name detail
----------- ---------- ------------
1 张山 yyy
1 张山 ddd
1 张山 fff
1 张山 ....
2 李四 www
2 李四 zzz
2 李四 ...
3 王五 qqq
3 王五 ....(所影响的行数为 9 行)
--*/