表1 A_B(NIAN CHAR(4),YUE CHAR(2),SL DECIMAL(14,2),P CHAR(20)) 2006-01,01,100,AEWE11121
2006-01 ,01,2000,1234
2006-01,01,1000, 1234
2006-02,01,1000, 1234
得到: 2006-01,01,3100,AEWE11121,1234
2006-02,01,1000, 1234
2006-01 ,01,2000,1234
2006-01,01,1000, 1234
2006-02,01,1000, 1234
得到: 2006-01,01,3100,AEWE11121,1234
2006-02,01,1000, 1234
我不是高手,但你的要求不适合用拼sql语句的方法 -_-
into #temp_1
from A_B
group by NIAN,YUE2.其次创建临时表#temp_2.select NIAN,YUE,distinct P
into #temp_2
from A_B
group by NIAN,YUE
3.最后创建临时表#temp_3.select a.NIAN,a.YUE,a.sum(SL),p=(Rtrim(b.p)+',')
into #temp_3
from #temp_1 a,#temp_2 b
where a.NIAN=b.NIAN,a.YUE=b.YUE4.更新末尾的','.
update #temp_3
set p=stuff(p,length(p),1,'')5.查询结果.
select * from #temp_3
create table A_B(NIAN CHAR(4),YUE CHAR(2),SL DECIMAL(14,2),P CHAR(20))
insert into A_B select '2006-01 ','01 ',100,'AEWE11121'
insert into A_B select '2006-01 ','01 ',2000,'1234'
insert into A_B select '2006-01 ','01 ',1000,'1234'
insert into A_B select '2006-02 ','01 ',1000,'1234'
go--创建用户定义函数
create function f_str(@nian char(4),@yue CHAR(2))
returns varchar(100)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+p from A_B where NIAN = @nian and YUE=@yue
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select NIAN,YUE,sl=sum(sl),name=dbo.f_str(NIAN,YUE) from A_B group by NIAN,YUE
go--输出结果 自己看
--删除测试数据
drop function f_str
drop table A_B
go
insert A_B
select '2006-01','01',100, 'AEWE11121' union all
select '2006-01','01',2000,'1234' union all
select '2006-01','01',1000,'1234' union all
select '2006-02','01',1000,'1234'
select * from A_B
gocreate function F_Str(@NIAN char(7),@YUE char(2))
returns varchar(250)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+ Rtrim(P) from A_B where [NIAN]=@NIAN and [YUE]=@YUE group by P --STUFF ( character_expression1 , start , length ,character_expression2).
--字符串char1从start开始的第length位字符用char2字符串代替
return stuff(@sql,1,1,'')
end
goselect NIAN,YUE,sum(sl) as SL,dbo.F_Str(NIAN,YUE) txt from A_B group by NIAN,YUEdrop function F_Str
drop table A_B