日期:2014-05-17 浏览次数:20594 次
if object_id('[ta]') is not null drop table [ta]
go
create table [ta](id int, 付款方式 varchar(3),付款金额 nvarchar(10))
insert [ta]
select 1 ,'001', '现金' union all
select 2 ,'002', '银行卡' union all
select 2 ,'003', '支票'
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](id int,商家 varchar(4), 付款方式 varchar(3),付款金额 int)
insert [tb]
select 1 ,'1001','001', '100' union all
select 2 ,'1001','002', '200' union all
select 3 ,'1001','003', '300' union all
select 4 ,'1002','001', '150' union all
select 5 ,'1002','003', '250'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' , max(case b.付款方式 when ''' + 付款方式 + ''' then b.付款金额 else 0 end) [' + 付款金额 + ']'
from ta
set @sql = 'select right(b.商家,1)id,b.商家,sum(b.付款金额)总金额'+@sql + ' from tb b join ta a on a.付款方式=b.付款方式 group by right(b.商家,1),商家'
exec(@sql)
--结果
/*
id 商家 总金额 现金 银行卡 支票
---- ---- ----------- ----------- ----------- -----------
1 1001 600 100 200 &