如何行列转换并对每一行合计

Question

目标：需要对奖金项数不确定的结果集中计算每个单位的各项奖金及总奖金
说明：
奖金项表tb_item(奖金项ID，奖金项名称)
奖金信息表tb（奖金项ID，得奖单位ID，奖金额）
【奖金信息表】中的[奖金项ID]参照【奖金项表】中的[奖金项ID]目前我通过用pivot行列转换已实现了部分结果（参考辅助SQL），但不知道如何对每一行的结果进行总计，因为列数不确定。请教各位该如何修改或实现？先拜谢各位大虾了
--辅助SQL（包括数据初始化，可直接执行看结果更清晰）
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
IF OBJECT_ID('[tb_item]') IS NOT NULL DROP TABLE [tb_item]--奖金项表
create table tb_item  (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10--select * from tbdeclare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + name from tb_item group by nameexec('
select * from (select x.cid,x.amount,i.name from tb x
inner join tb_item i on x.itemid=i.itemid) tb_r pivot (max(amount) for name in ('+@sql+')) t')--目前以上语句已经能实现以下结果
select 'c1' as cid,'50' as A奖金,'30' as B奖金,'20' as C奖金 union all
select 'c2','25','10',null
--需要达到的目标结果
select 'c1' as cid,'50' as A奖金,'30' as B奖金,'20' as C奖金,'100' as total union all
select 'c2','25','10','0','35'    

Answer 1

如果可以，希望能不用EXEC这样拼接字符串的语法。

Answer 2

--奖金项表
create table tb_item  (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10--select * from tbdeclare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + name from tb_item group by name
select @sql = @sql + ',合计'exec('
select * from (select x.cid,sum(x.amount) amount,isnull(i.name,''合计'') name from tb x
inner join tb_item i on x.itemid=i.itemid group by x.cid,i.name with rollup) tb_r pivot (max(amount) for name in ('+@sql+')) t where cid is not null')drop table tb_item,tb/**********cid                                                A奖金                                     B奖金                                     C奖金                                     合计
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
c1                                                 50                                      30                                      20                                      100
c2                                                 25                                      10                                      NULL                                    35(2 行受影响)

Answer 3

有没有不采用EXEC的方法，直接select出来的？因为系统某些特殊原因不支持EXEC

Answer 4

如果你能确定奖金项有多少个，是固定的，而且名称你都知道，那么可以直接select。

Answer 5

--奖金项表
create table tb_item  (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10select * from 
(select x.cid,sum(x.amount) amount,isnull(i.name,'合计') name 
from tb x
inner join 
tb_item i on x.itemid=i.itemid 
group by x.cid,i.name with rollup) tb_r 
pivot (max(amount) for name in (A奖金,B奖金,C奖金,合计)) t
--where cid is not null
order by cid descdrop table tb_item,tb/*************cid                                                A奖金                                     B奖金                                     C奖金                                     合计
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
c2                                                 25                                      10                                      NULL                                    35
c1                                                 50                                      30                                      20                                      100
NULL                                               NULL                                    NULL                                    NULL                                    135(3 行受影响)

Answer 6

不确定列数。开始想过在for name in (select name from tb_item group by name)，但没有这样的语法。目前系统的架构是在我的语句外包一层SQL做权限控制。结果就出现类似select * from (exec('...'))这样的语法错误。

Answer 7

exec('...')插入臨時表，select * from (臨時表)，不過還是用存儲過程吧

Answer 8

需要用一个临时表来调用
insert into #t exec...

Answer 9

应该说外面因为包了一层SELECT，所以临时表和EXEC的语法也用不了，必须能够符合SELECT * from (实现语句)这种格式的语法才行。

Answer 10

没有固定的列没办法直接select出来。