We can understand the
difference between ROLLUP and CUBE with a simple example. Consider we have a
table which contains the results of quarterly test of students. In certain
cases we need to see the total corresponding to the quarter as well as the students.
Here is the sample table
SELECT * INTO #TEMP
FROM
(
SELECT 'Quarter 1' PERIOD,'Amar' NAME ,97 MARKS
UNION ALL
SELECT 'Quarter 1','Ram',88
UNION ALL
SELECT 'Quarter 1','Simi',76
UNION ALL
SELECT 'Quarter 2','Amar',94
UNION ALL
SELECT 'Quarter 2','Ram',82
UNION ALL
SELECT 'Quarter 2','Simi',71
UNION ALL
SELECT 'Quarter 3' ,'Amar',95
UNION ALL
SELECT 'Quarter 3','Ram',83
UNION ALL
SELECT 'Quarter 3','Simi',77
UNION ALL
SELECT 'Quarter 4' ,'Amar',91
UNION ALL
SELECT 'Quarter 4','Ram',84
UNION ALL
SELECT 'Quarter 4','Simi',79
)TAB
1. ROLLUP(Can find total for corresponding to one
column)
(a) Get total score of
each student in all quarters.
SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD
WITH ROLLUP
HAVING PERIOD IS NULL AND NAME IS NOT NULL
// Having is used inorder to emit a row that is the total of all totals of each
student
Following is the result
of (a)
(b) Incase you need to
get total score of each quarter
SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY PERIOD,NAME
WITH ROLLUP
HAVING PERIOD IS NOT NULL AND NAME IS NULL
Following is the
result of (b)
2. CUBE(Find total for Quarter as well as students in
a single shot)
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD
WITH CUBE
HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL
Following is the
result of CUBE
Now you may be
wondering about the real time use of ROLLUP and CUBE. Sometimes we need a
report in which we need to see the total of each quarter and total of each
student in a single shot. Here is an example
I am changing the
above CUBE query slightly as we need total for both totals.
SELECT CASE WHEN PERIOD IS NULL THEN 'TOTAL' ELSE PERIOD END PERIOD,
CASE WHEN NAME IS NULL THEN 'TOTAL' ELSE NAME END NAME,
SUM(MARKS)
MARKS
INTO #TEMP2
FROM #TEMP
GROUP BY NAME,PERIOD
WITH CUBE
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + PERIOD + ']',
'[' + PERIOD + ']')
FROM (SELECT DISTINCT PERIOD FROM #TEMP2)
PV
ORDER BY PERIOD
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM
(
SELECT * FROM #TEMP2
) x
PIVOT
(
SUM(MARKS)
FOR [PERIOD] IN (' + @cols + ')
) p;'
EXEC SP_EXECUTESQL @query
Now you will get the
following result
