22,209
社区成员
发帖
与我相关
我的任务
分享
declare @i int, @cnt int ,@sql nvarchar(4000)
select @i=1,@cnt = max(id) , @sql =' SELECT [客户] '
from (select id=(select count(1) from # where [客户] = t.[客户]),[客户],[金额] from # t) a
while @i<=@cnt
begin
select @sql = @sql + ' , isnull(max(case when id ='+ltrim(@i)+' then [金额] end),0) as '' 第'+ltrim(@i)+'笔''' --加上ISNULL
set @i =@i+1
end
select @sql = @sql +' from (select id=(select count(1) from # '
+' where [客户] = t.[客户] and [px] <=t.[px]),[客户],[金额] from # t) a group by [客户] '
exec(@sql)
-->生成测试数据
drop table #
go
declare @tb table([客户] nvarchar(1),[金额] int)
Insert @tb
select N'A',500 union all
select N'B',400 union all
select N'B',600
Select px=identity(int,1,1),* into # from @tb
select id=(select count(1) from # where [客户] = t.[客户]),[客户],[金额] from # t
declare @i int, @cnt int ,@sql nvarchar(4000)
select @i=1,@cnt = max(id) , @sql =' SELECT [客户] '
from (select id=(select count(1) from # where [客户] = t.[客户]),[客户],[金额] from # t) a
while @i<=@cnt
begin
select @sql = @sql + ' , max(case when id ='+ltrim(@i)+' then [金额] end) as '' 第'+ltrim(@i)+'笔'''
set @i =@i+1
end
select @sql = @sql +' from (select id=(select count(1) from # '
+' where [客户] = t.[客户] and [px] <=t.[px]),[客户],[金额] from # t) a group by [客户] '
exec(@sql)
/*
客户 第1笔 第2笔
---- ----------- -----------
A 500 NULL
B 400 600
*/