将一个表中的多条记录插入到另一个表中作为一条记录
一个表结构如下: id name contetID1 test 1
2 test1 1
3 test2 1
4 test3 2
5 test4 2
把它的记录加入另一个表中，结构如下：
contetID names
1 test,test1,test2
2 test3,test4
应如何写sql语句 --------------------编程问答--------------------
--------------------编程问答--------------------
if OBJECT_ID('A','U')is not null drop table A
go
create table A
(
id int, name nvarchar(10), contetID int
)
go
insert into A
select 1, 'test', 1 union all
select 2, 'test1', 1 union all
select 3, 'test2', 1 union all
select 4, 'test3', 2 union all
select 5, 'test4', 2
go
select distinct contetID,
name=stuff((select ','+name from A as b where a.contetID=b.contetID for XML path('')),1,1,'')
from A as a
/*
contetID name
----------- ----------------
1 test,test1,test2
2 test3,test4
*/
--将上面的结果直接插入另一张表中即可
insert into othertb
select distinct contetID,
name=stuff((select ','+name from A as b where a.contetID=b.contetID for XML path('')),1,1,'')
from A as a
补充：.NET技术 , C#
