declare @t1 table(系统编号 int,单位列表 varchar(20)) insert into @t1 select 1,'a;b;' insert into @t1 select 2,'f;b;'set rowcount 1000 select identity(int,1,1) as id into # from sysobjects set rowcount 0select e.系统编号,substring(e.单位列表,f.id1,(f.id2-f.id1-1)) 单位名称 from @t1 e, (select c.系统编号,c.id id1,min(d.id) id2 from (select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) c, (select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) d where c.系统编号=d.系统编号 and c.id<d.id group by c.系统编号,c.id) f where e.系统编号=f.系统编号/* 系统编号 单位名称 ----------- -------------------- 1 a 2 f 1 b 2 b */drop table #
declare @t1 table(系统编号 int,单位列表 varchar(20)) insert into @t1 select 1,'a;b;' insert into @t1 select 2,'f;b;'set rowcount 1000 select identity(int,1,1) as id into # from sysobjects set rowcount 0select e.系统编号,substring(e.单位列表,f.id1,(f.id2-f.id1-1)) 单位名称 from @t1 e, (select c.系统编号,c.id id1,min(d.id) id2 from (select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) c, (select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) d where c.系统编号=d.系统编号 and c.id<d.id group by c.系统编号,c.id) f where e.系统编号=f.系统编号 order by e.系统编号/* 系统编号 单位名称 ----------- -------------------- 1 a 1 b 2 f 2 b */drop table #
insert into @t1 select 1,'a;b;'
insert into @t1 select 2,'f;b;'set rowcount 1000
select identity(int,1,1) as id into # from sysobjects
set rowcount 0select
e.系统编号,substring(e.单位列表,f.id1,(f.id2-f.id1-1)) 单位名称
from
@t1 e,
(select
c.系统编号,c.id id1,min(d.id) id2
from
(select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) c,
(select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) d
where
c.系统编号=d.系统编号 and c.id<d.id
group by
c.系统编号,c.id) f
where
e.系统编号=f.系统编号/*
系统编号 单位名称
----------- --------------------
1 a
2 f
1 b
2 b
*/drop table #
insert into @t1 select 1,'a;b;'
insert into @t1 select 2,'f;b;'set rowcount 1000
select identity(int,1,1) as id into # from sysobjects
set rowcount 0select
e.系统编号,substring(e.单位列表,f.id1,(f.id2-f.id1-1)) 单位名称
from
@t1 e,
(select
c.系统编号,c.id id1,min(d.id) id2
from
(select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) c,
(select a.系统编号,b.id from @t1 a,# b where substring(';'+a.单位列表,b.id,1)=';' and b.id<=len(a.单位列表)+1) d
where
c.系统编号=d.系统编号 and c.id<d.id
group by
c.系统编号,c.id) f
where
e.系统编号=f.系统编号
order by
e.系统编号/*
系统编号 单位名称
----------- --------------------
1 a
1 b
2 f
2 b
*/drop table #