个人和整体水平的总和

寻找输出作为

我试着用汇总,立方体,分组集,但似乎没有什么是合适的。

declare @t table(

[Employee Name] varchar(50),Bucket int,
[Start Inventory No] int ,[Start Inventory Amount] int,
[No Of Promise to Pay] int,[Promise to Pay Amount] int)

insert into @t 
    select 'A', 0,10,10000,3,100 union all
    select 'A', 1,20,20000,7,500 union all
    select 'B', 0,45,90000,4,200 union all
    select 'B', 1,12,70000,6,600 union all
    select 'c', 0,16,19000,1,500 union all
    select 'c', 1,56,9000,10,2500

select 
    [Employee Name] 
    ,Bucket=case when x.rn= 11 then 'total' else Bucket end
    ,[Start Inventory No]= case when x.rn= 11 then sum([Start Inventory No]) else [Start Inventory No] end

from 
(select 
rn=ROW_NUMBER() Over(partition by [Employee Name] order by (select 1)), 
    *
from @t
GROUP BY 
        Rollup
        ([Employee Name] ,Bucket,[Start Inventory No],[Start Inventory Amount],[No Of Promise to Pay],
        [Promise to Pay Amount]))X where x.Rn in (1,6,11)

group by [Employee Name] 
    ,Bucket, rn