sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | ROLLUP @ GROUP BY

WITH TTC([KEY], [TYPE], VALUE1, VALUE2)
AS
(
 SELECT * FROM
 (
  VALUES
   ('A', 'T1', 700, 1300),
   ('A', 'T1', 70, 130),
   ('A', 'T1', 7, 13),
   ('A', 'T2', 70, 130),
   ('B', 'T1', 50, 110),
   ('B', 'T1', 70, 130),
   ('B', 'T3', 5, 11),
   ('C', 'T3', 5, 11)
 ) TTC([KEY], [TYPE], VALUE1, VALUE2)
)
SELECT [KEY], [TYPE], VALUE1, VALUE2, SUM(VALUE1) SUM1, SUM(VALUE2) SUM2
FROM TTC
GROUP BY ROLLUP ([KEY], [TYPE], VALUE1, VALUE2);
 
LV1 LV2 LV3 LV4
A1 T1 7 13 7 13 A ('A', 'T1', 7, 13)
A1 T1 7 NULL 7 13 A LV3 with T1 A
A1 T1 70 130 70 130 B ('A', 'T1', 70, 130)
A1 T1 70 NULL 70 130 B LV3 with T1 B
A1 T1 700 1300 700 1300 C ('A', 'T1', 700, 1300)
A1 T1 700 NULL 700 1300 C LV3 with T1 C
A1 T1 NULL NULL 777 1443 X = A + B + C ALL LV3 with T1
A1 T2 70 130 70 130 D ('A', 'T2', 70, 130)
A1 T2 70 NULL 70 130 D LV3 with T2 Y
A1 T2 NULL NULL 70 130 Y = D ALL LV3 with T2
A1 NULL NULL NULL 847 1573 Z = X + Y ALL LV2 with LV1 A1
B1 T1 50 110 50 110 E ('B', 'T1', 50, 110)
B1 T1 50 NULL 50 110 E LV3 with T1 D
B1 T1 70 130 70 130 F ('B', 'T1', 70, 130)
B1 T1 70 NULL 70 130 F LV3 with T1 E
B1 T1 NULL NULL 120 240 Q = E + F ALL LV3 with T1
B1 T3 5 11 5 11 G ('B', 'T3', 5, 11)
B1 T3 5 NULL 5 11 G LV3 with T3 F
B1 T3 NULL NULL 5 11 R = G ALL LV3 with T3
B1 NULL NULL NULL 125 251 S = Q + R ALL LV2 with LV1 B1
C1 T3 5 11 5 11 H ('C', 'T3', 5, 11)
C1 T3 5 NULL 5 11 H LV3 with T3 H
C1 T3 NULL NULL 5 11 M = H ALL LV3 with T3
C1 NULL NULL NULL 5 11 N = M ALL LV2 with LV1 C1
NULL NULL NULL NULL 977 1835 α = Z + Q + N ALL LV1
Comments