日期:2014-05-17 浏览次数:20591 次
create table dri
(A int, B int, C varchar(10))
insert into dri
select 1, 300, 'a1' union all
select 1, 200, 'a2' union all
select 2, 500, 'b1' union all
select 2, 300, 'b2'
select a.A,
sum(a.B) 'B',
stuff((select ','+C
from dri b
where b.A=a.A
for xml path('')),1,1,'') 'C'
from dri a
group by a.A
/*
A B C
----------- ----------- ------------
1 500 a1,a2
2 800 b1,b2
(2 row(s) affected)
*/
if exists(select * from sysobjects where name ='TT')
drop table TT
go
create table TT
(
A int,
B int,
C varchar(10)
)
insert into TT
select 1,300, 'a1' union all
select 1,200, 'a2' union all
select 2,500, 'b1' union all
select 2,300, 'b2'
go
select A,sum(isnull(B,0))B,
stuff((select ','+C from TT x where x.A=y.A for xml path('')),1,1,'')C
from TT y group by A