表a中有个字段temp里面放的数据是
aid temp
1 12,112,23,435,211,
2 45,56,23,44
3 11,14
4 11,45,66,7
就是我选择aid为1的一条记录,把temp中的这些都竖着列出啊,插入到另一个表中!
1 12
1 112
1 2
1 435
1 211
如何写啊??
谢啦各位老大
aid temp
1 12,112,23,435,211,
2 45,56,23,44
3 11,14
4 11,45,66,7
就是我选择aid为1的一条记录,把temp中的这些都竖着列出啊,插入到另一个表中!
1 12
1 112
1 2
1 435
1 211
如何写啊??
谢啦各位老大
(aid int, temp varchar(100))
insert into tb
select 1, '12,112,23,435,211' union all
select 2, '45,56,23,44' union all
select 3, '11,14' union all
select 4, '11,45,66,7'
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.aid,
temp = SUBSTRING(A.temp, B.ID, CHARINDEX(',', A.temp + ',', B.ID) - B.ID)
FROM tb A, # B
WHERE SUBSTRING(',' + a.temp, B.id, 1) = ','
and aid=1
ORDER BY 1,2
GOaid temp
----------- ----------------------------------------------------------------------------------------------------
1 112
1 12
1 211
1 23
1 435(所影响的行数为 5 行)
insert into a values(1,'12,112,23,435,211,')
insert into a values(2,'45,56,23,44')
insert into a values(3,'11,14')
insert into a values(4,'11,45,66,7')create table b(aid int,temp varchar(20))
declare @sql varchar(8000),@aid intset @aid=1select
@sql='insert into b select '+rtrim(@aid)+','+replace((case right(temp,1) when ',' then left(temp,len(temp)-1) else temp end),',',' union select '+rtrim(@aid)+',')
from
a
where
aid=@aidprint @sqlexec(@sql)select * from b
/*
aid temp
----------- --------------------
1 112
1 12
1 211
1 23
1 435
*/drop table a,b
set @a='12,112,23,435,211'
set @a=replace(@a,',',' union all select ')
exec('select '+@a)
create table #t (aid int,tempid int)
declare @sql varchar(8000)
set @sql = ''
select @sql = repalce(','+temp,',' from a where aid = 1 union all select aid,tempid =')
from a where aid = 1
set @sql = stuff(@sql,1,33,'')
set @sql = @sql + 'from a where aid = 1'
insert #t
exec(@sql)
aid temp 1 112
1 12
1 211
1 23
1 435
2 23
2 44
2 45
2 56
3 11
3 14
4 11
4 45
4 66
4 7(所影响的行数为 15 行)
create table #t (aid int,tempid int)
declare @sql varchar(8000)
set @sql = ''
select @sql = repalce(','+temp,',',' from a where aid = 1 union all select aid,tempid =')
from a where aid = 1
set @sql = stuff(@sql,1,33,'')
set @sql = @sql + 'from a where aid = 1'
insert #t
exec(@sql)
@aid int
as
declare @sql varchar(8000)
declare @temp varchar(1000)
select @temp=temp from a where aid=@aid
set @sql='insert into 表 select '+replace(@temp,',',' union all select ')
exec(@sql)
go
insert into a values(1,'12,112,23,435,211,')
insert into a values(2,'45,56,23,44')
insert into a values(3,'11,14')
insert into a values(4,'11,45,66,7')create table b(aid int,temp varchar(20))go
declare @sql varchar(8000)
set @sql = ''
select
@sql=@sql+' union all select '+
rtrim(aid)+','+
replace((case right(temp,1) when ',' then left(temp,len(temp)-1) else temp end),',',' union select '+rtrim(aid)+',')
from
a
set @sql = right(@sql,Len(@sql) - 11)exec('insert into b '+@sql)select * from bdrop table a,b
/*aid temp
----------- --------------------
1 112
1 12
1 211
1 23
1 435
2 23
2 44
2 45
2 56
3 11
3 14
4 11
4 45
4 66
4 7(所影响的行数为 15 行)*/
insert into tb values(1,'12,112,23,435,211')
insert into tb values(2,'45,56,23,44')
insert into tb values(3,'11,14')
insert into tb values(4,'11,45,66,7')
go-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b SELECT A.aid, [temp] = SUBSTRING(A.[temp], B.ID, CHARINDEX(',', A.[temp] + ',', B.ID) - B.ID)
FROM tb A, tmp B
WHERE SUBSTRING(',' + a.[temp], B.id, 1) = ','
ORDER BY 1
GOdrop table tb,tmp/*
aid temp
----------- --------------------------------------------------
1 12
1 112
1 23
1 435
1 211
2 45
2 56
2 23
2 44
3 11
3 14
4 11
4 45
4 66
4 7(所影响的行数为 15 行)
*/