DECLARE @t TABLE (RowNum INT IDENTITY(1,1),Grp CHAR(1), Val INT)
INSERT INTO @t
SELECT NULL, 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 4 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 5
SELECT d.Grp,d.GroupTotal
FROM (
SELECT RowNum,
Grp = CASE
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 0 THEN Grp
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 1 THEN 'Sub Total ' + ISNULL(grp,'NULL')
ELSE 'Grand Total'
END,
GroupTotal = SUM(Val)
FROM @t
GROUP BY Grp,Val,RowNum WITH ROLLUP
) d
WHERE RowNum > 0 OR Grp LIKE '%Total%'
Grp GroupTotal
NULL 1
Sub Total N 1
A 2
A 3
A 3
A 3
Sub Total A 11
B 4
B 5
Sub Total B 9
Grand Total 21
INSERT INTO @t
SELECT NULL, 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 4 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'B', 5
SELECT d.Grp,d.GroupTotal
FROM (
SELECT RowNum,
Grp = CASE
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 0 THEN Grp
WHEN GROUPING(Grp) = 0 AND GROUPING(Val) = 1 THEN 'Sub Total ' + ISNULL(grp,'NULL')
ELSE 'Grand Total'
END,
GroupTotal = SUM(Val)
FROM @t
GROUP BY Grp,Val,RowNum WITH ROLLUP
) d
WHERE RowNum > 0 OR Grp LIKE '%Total%'
Grp GroupTotal
NULL 1
Sub Total N 1
A 2
A 3
A 3
A 3
Sub Total A 11
B 4
B 5
Sub Total B 9
Grand Total 21
No comments:
Post a Comment