tsql - 个人和整体水平的总和

Question

我有一些东西

寻找输出为

我正在尝试使用 RollUp、cube、Grouping Set，但似乎没有任何东西适合。

这是我不成功的尝试：

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

标签： tsqlsql-server-2016

Answer 1

这应该在客户端而不是服务器上使用数据透视表来完成。

如果出于某种原因您确实想从第一张桌子到达第二张桌子，我会这样做

select
  case when grouping(fake_column) = 1 then null else [Employee Name] end as [Employee Name],
  case when grouping([Employee Name]) = 1 and grouping(fake_column) = 1 then 'Gran Total' when grouping(fake_column) = 1 then 'Total' else cast(sum(Bucket) as varchar) end as Bucket,
  sum([Start Inventory No]) as [Start Inventory No],
  sum([Start Inventory Amount]) as [Start Inventory Amount],
  sum([No Of Promise to Pay]) as [No Of Promise to Pay],
  sum([Promise to Pay Amount]) as [Promise to Pay Amount]
from
  (select *, row_number() over(partition by [Employee Name] order by 1/0) as fake_column from @t) data
group by
  rollup([Employee Name], fake_column)
;

这个想法是通过引入一个假列使每一行唯一，并将该列包含在分组中，以便原始行也作为“分组”结果出现（每个“组”由于唯一编号而包含一行） .