set rowcount 100
select identity(int,1,1) as id into #T from sysobjects
set rowcount 0insert into table2
select
c.ipid,c.webid
from
(select
a.webid,
ipid = substring(iptype,b.id,1)
from
table1 a,
#t b
where
b.id <= len(a.iptype)) c
where
c.ipid !=',' and c.ipid != ' '
order by
c.ipid
select identity(int,1,1) as id into #T from sysobjects
set rowcount 0insert into table2
select
c.ipid,c.webid
from
(select
a.webid,
ipid = substring(iptype,b.id,1)
from
table1 a,
#t b
where
b.id <= len(a.iptype)) c
where
c.ipid !=',' and c.ipid != ' '
order by
c.ipid
create table table1 (webid int,iptype varchar(8000))
insert table1 select 1,'1,2'
insert table1 select 2,'3'
insert table1 select 3,'4,5,6'
insert table1 select 4,'7'
create table table2(ipid varchar(20),webid int)--创建过程
create proc ptest
as begin
declare @s varchar(8000)
declare @i as int
declare @b varchar(20)
declare @c int
set @c=1
while @c<=(select max(webid) from table1) begin
select @s=iptype from table1 where webid=@c
set @i=charindex(',',@s)
while @i>0
begin
set @b=left(@s,@i-1)
insert table2 values(@b,@c)
set @s=right(@s,len(@s)-@i)
set @i=charindex(',',@s)
end
if len(@s)>0
begin
insert table2 values(@s,@c)
end
set @c=@c+1
end
end
go--调用过程
exec ptest
select * from table2--结果
ipid webid
-------------------- -----------
1 1
2 1
3 2
4 3
5 3
6 3
7 4(所影响的行数为 7 行)
(
webid int,
iptype varchar(20)
)
insert @tb select 1,'1,2' union
select 2,'3' union
select 3,'4,5,6' union
select 4,'7' --测试
select identity(int,1,1) ID into # from syscolumns a,syscolumns bselect B.ID
,A.webid
from @tb A
join # B on charindex(','+convert(varchar,B.ID)+',',','+A.iptype+',')>0drop table #--结果
/*
ID webid
----------- -----------
1 1
2 1
3 2
4 3
5 3
6 3
7 4(所影响的行数为 7 行)
*/