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