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) */
要求不难,难点估计是楼主对for xml path 不清楚
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
SELECT A,SUM(B) B, C = STUFF((SELECT ',' + CAST(C AS NVARCHAR(MAX)) FROM dri T1 WHERE (A = T2.A) FOR XML PATH ('')),1,1,'') FROM dri T2 GROUP BY A 运行结果:
正在研究这个问题,看有没有不用 open xml
create table tk(t1 int,t2 int ,t3 varchar(500))insert into tk select 1,300,'a1' union all select 1,200,'a2' union all select 2,500,'b1' union all select 2,300,'b2' create function gid(@id int) returns varchar(1000) as begin declare @t varchar(1000) select @t=isnull(@t,'')+','+t3 from tk where t1=@id return stuff(@t,1,1,'') endselect t1 a,sum(t2) b ,dbo.gid(t1) c from tk group by t1
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)
*/
要求不难,难点估计是楼主对for xml path 不清楚
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
FROM dri T1 WHERE (A = T2.A)
FOR XML PATH ('')),1,1,'')
FROM dri T2
GROUP BY A 运行结果:
create table tk(t1 int,t2 int ,t3 varchar(500))insert into tk select 1,300,'a1'
union all
select 1,200,'a2'
union all
select 2,500,'b1'
union all
select 2,300,'b2'
create function gid(@id int) returns varchar(1000)
as
begin
declare @t varchar(1000)
select @t=isnull(@t,'')+','+t3 from tk where t1=@id
return stuff(@t,1,1,'')
endselect t1 a,sum(t2) b ,dbo.gid(t1) c from tk group by t1