有表
table1id name id21 a x1 a y1 a z table2id name id21 a null现在想让table2的id2变为table2id name id21 a x,y,z该怎么操作
table1id name id21 a x1 a y1 a z table2id name id21 a null现在想让table2的id2变为table2id name id21 a x,y,z该怎么操作
FROM #table1 a
group by id,name
if object_id('tb1')is not null
drop table tb1
go
create table tb1(id int,name varchar(4),id2 varchar(4))
insert into tb1
select '1','a','x' union all
select '1','a','y' union all
select '1','a','z' if object_id('tb2')is not null
drop table tb2
go
create table tb2(id int,name varchar(4),id2 varchar(4))
insert into tb2
select '1','a',null-- select * from tb1
-- select * from tb2
-->测试查询
select id,name,id2=stuff((select ','+id2 from tb1 for xml path('') ),1,1,'' )
from tb2 a
group by id,name
-->测试结果/*
---- -------- ------------
id name id2
1 a x,y,z
*/
update tb2 set id2=stuff((select ','+id2 from tb1 for xml path('') ),1,1,'' )