一条复杂的sql语句
我想写一条SELECT 语句。
表内容如下:
A B C
1 300 a1
1 200 a2
2 500 b1
2 300 b2
我要得到如下内容:
A B C
1 500 a1,a2
2 800 b1,b2
按A分组,B是求和,C是内容连接
------解决方案--------------------
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,'')
end
select t1 a,sum(t2) b ,dbo.gid(t1) c from tk group by t1