--生成测试数据
create table 表(客户 varchar(20),金额 int,备注 varchar(20))
insert into 表 select 'A137',100,'SO0001'
insert into 表 select 'A137',241,'SO0002'go--创建用户定义函数
create function f_str(@client varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+'/'+right(备注,2) from 表 where 客户 = @client
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 客户,sum(金额),[备注1]=dbo.f_str(客户) from 表 group by 客户 order by 客户
go--输出结果
/*
客户 备注1
-------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A137 341 01/02
*/
--删除测试数据
drop function f_str
drop table 表
go
create table 表(客户 varchar(20),金额 int,备注 varchar(20))
insert into 表 select 'A137',100,'SO0001'
insert into 表 select 'A137',241,'SO0002'go--创建用户定义函数
create function f_str(@client varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+'/'+right(备注,2) from 表 where 客户 = @client
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 客户,sum(金额),[备注1]=dbo.f_str(客户) from 表 group by 客户 order by 客户
go--输出结果
/*
客户 备注1
-------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A137 341 01/02
*/
--删除测试数据
drop function f_str
drop table 表
go
declare @s varchar(8000)
declare @cust varchar(20)
declare @flg int
set @flg=0
select top 1 @cust=cust from #t group by cust order by cust
set @s='select '+''''+@cust+''''+' as cust,'''
select @flg=case when cust<>@cust then @flg+1 else @flg end,@s=
case cust
when @cust then @s+right(memo,2)+'/'
else substring(@s,1,len(@s)-1)+''''+case @flg when 1 then ' as memo into #tab' else '' end +' union select '+''''+cust+''''+','+''''+right(memo,2)+'/'
end ,@cust=cust
from #t
order by cust
set @s=substring(@s,1,len(@s)-1)+''''+' select a.cust,sum(b.price) as price ,a.memo From #tab a,#t b where a.cust=b.cust group by a.cust,a.memo'
exec(@s)
insert into #t select 'A137',100,'SO0001'
insert into #t select 'A137',241,'SO0002'insert into #t select 'A138',80,'SO0003'
insert into #t select 'A138',85,'SO0004'insert into #t select 'A139',44,'SO0005'
insert into #t select 'A139',46,'SO0006'godeclare @s varchar(8000)
declare @cust varchar(20)
declare @flg int
set @flg=0
select top 1 @cust=cust from #t group by cust order by cust
set @s='select '+''''+@cust+''''+' as cust,'''
select @flg=case when cust<>@cust then @flg+1 else @flg end,@s=
case cust
when @cust then @s+right(memo,2)+'/'
else substring(@s,1,len(@s)-1)+''''+case @flg when 1 then ' as memo into #tab' else '' end +' union select '+''''+cust+''''+','+''''+right(memo,2)+'/'
end ,@cust=cust
from #t
order by cust
set @s=substring(@s,1,len(@s)-1)+''''+' select a.cust,sum(b.price) as price ,a.memo From #tab a,#t b where a.cust=b.cust group by a.cust,a.memo'
exec(@s)