个人和整体层面的总结

Question

我有件事

​

​

查找输出，如

​

​

我正在尝试使用RollUp、多维数据集、分组集，但似乎没有任何东西适合。

以下是我失败的尝试：

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

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)
;

这样做的想法是通过引入一个假列使每一行都是唯一的，并在分组中包含该列，这样原始行也会以“分组”的形式出现(每个“组”由于唯一的编号而包含一行)。