MSSQL请教旧表导入到新表的语句
oldtb --这是原来的数据
id aid itemid
2 3 111
4 3 222
6 4 333
7 3 666
newtb --这是希望写入到另一个表的一个字段里
aid itemIds
3 111,222,666
4 333
有一个旧表oldtb，想要更换表结构，把原来的数据导到另一个表的一个新建字段里newtb
请问这语句应该怎么写啊。
[解决办法]
if object_id('test') is not null drop table test
create table test(id int,aid int,itemid int)
insert into test select 2,3,111
union all
select 4,3,222
union all
select 6,4,333
union all
select 7,3,666
declare @t1 table(aid int,content nvarchar(100))
declare @aid int
declare test_add cursor for select aid from test group by aid
open test_add
fetch next from test_add into @aid
while @@fetch_status=0
begin
declare @t nvarchar(100)
select @t=isnull(@t,'')+','+cast(itemid as varchar(30)) from test where aid=@aid
insert @t1 select @aid,stuff(@t,1,1,'')
set @t=''
fetch next from test_add into @aid
end
close test_add
deallocate test_add
select * from @t1
insert into newtb
select
aid,itemIds=stuff((select ','+ltrim([itemId]) from oldtb where aid=t.aid for xml path('')), 1, 1, '')
from oldtb t
group by aid
create table oldtb
(id int,aid int,itemid int)
insert into oldtb
select 2,3,111 union all
select 4,3,222 union all
select 6,4,333 union all
select 7,3,666
create table newtb
(aid int,itemIds varchar(20))
insert into newtb(aid,itemIds)
select a.aid,
stuff((select ','+rtrim(b.itemid)
from oldtb b
where b.aid=a.aid
for xml path('')),1,1,'')
from oldtb a
group by a.aid
select * from newtb
/*
aid itemIds
----------- --------------------
3 111,222,666
4 333
(2 row(s) affected)
*/
