多层次结构的小计和合计

Question

我正在设置一个报表查询。我有多个层次的位置，我需要正确显示。

我试过使用分组汇总，但我得不到我需要的。

这是我的数据样本。

Tbl值

Branch  BranchName  Cluster Location    District    Value
0001      A           C1      Loc1         District1    1000
0002      B           C1      Loc1         District1    2000
0003      C           C2      Loc2         District1    3000
0004      D           C2      Loc2         District1    4000
0005      E           C3      Loc2         District1    5000

这就是我迄今为止所尝试的。

SELECT  CASE WHEN GROUPING(a.cluster) = 1 THEN a.District
WHEN GROUPING(a.branchname) = 1 THEN a.Cluster
ELSE a.BranchName
END ,
SUM(a.Value) Value
FROM    ( SELECT    '0001' Branch ,
'A' BranchName ,
'C1' Cluster ,
'Loc1' Location ,
'District1' District ,
1000 Value
UNION ALL
SELECT    '0002' ,
'B' ,
'C1' ,
'Loc1' ,
'District1' ,
2000
UNION ALL
SELECT    '0003' ,
'C' ,
'C2' ,
'Loc2' ,
'District1' ,
3000
UNION ALL
SELECT    '0004' ,
'D' ,
'C2' ,
'Loc2' ,
'District1' ,
4000
UNION ALL
SELECT    '0005' ,
'E' ,
'C3' ,
'Loc2' ,
'District1' ,
5000
) a
GROUP BY ROLLUP(a.Cluster, a.BranchName) ,
a.District;

这就是我的结果。

Loc Value
A   1000
B   2000
C1  3000
C   3000
D   4000
C2  7000
E   5000
C3  5000
District1   15000

我也想展示一下位置。像这样：

Loc Value
A   1000
B   2000
C1  3000
Loc1    3000
C   3000
D   4000
C2  7000
E   5000
C3  5000
Loc2    12000
District1   15000

Answer 1

declare @tbl as table (Branch varchar(max), 
BranchName varchar(max),
Cluster varchar(max),
Location varchar(max),
District varchar(max),
Value int)
INSERT INTO @tbl
select '0001' ,'A','C1','Loc1', 'District1',1000 UNION ALL
select '0002' ,'B','C1','Loc1', 'District1',2000 UNION ALL
select '0003' ,'C','C2','Loc2', 'District1' ,3000 UNION ALL
select '0004' ,'D','C2','Loc2', 'District1' ,4000 UNION ALL
select '0005' ,'E','C3','Loc2', 'District1' ,5000

;with cte as (SELECT case when BranchName is null and cluster is not null then Cluster
when BranchName is null and cluster is null and Location is not null then Location
when BranchName is null and cluster is null and Location is null and District is not null then District else BranchName end Loc
, Cluster, Location, District,  sum(Value) Value FROM @tbl
group by
grouping sets (
(District),
(Location, District),
(Cluster,Location, District),
(BranchName, Cluster, Location, District),
(Value)))
select Loc, value from cte where Loc is not null

Answer 2

使用Union ALL

FIDDLE演示

SELECT BranchName, Value FROM
(
SELECT * FROM table
) A
UNION ALL
SELECT Location, SUM(Value) FROM
(
SELECT * FROM table
) A GROUP BY Location
UNION ALL
SELECT Cluster, SUM(Value) FROM
(
SELECT * FROM table
) A GROUP BY Cluster
UNION ALL
SELECT District, SUM(Value) FROM
(
SELECT * FROM table
) A GROUP BY District