Subtotal and total of multiple levels of hierarchy

Question

I'm setting up a report query. I got multiple hierarchy of locations which I need to show correctly.

I've tried using group by rollup but I can't get what I need.

This is my sample of data.

TblValues

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

This is what I have tried so far.

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;

And this is my result.

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

I want to show the Location as well. Like this:

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

Use Union ALL

FIDDLE DEMO

   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