Advertisements
Home > SQL Tips > GROUPING SETS equivalent | for SQL Server 2005 and below

GROUPING SETS equivalent | for SQL Server 2005 and below


Sometime back I discussed about [GROUPING SETS] in my previous post and today one reader of this blog asked me how we can create the same result-set without using GROUPING SETS if we are on previous versions like SQL Server 2005 or 2000.
 

–> Let’s take the following SQL Query that uses GROUPING SETS operator and let’s see what Output we get:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

You can refer to the DDL and population of this #tempTable in my previous post, [link].
 

–> And now let’s see how can we recreate the same result set without using GROUPING SETS if we are on lower versions of SQL Server (<= 2005):

-- ROLLUP and Grouping Sets Equivalent (pre SQL 2008)
SELECT class, section, rollno, marks as [sum]
FROM #tempTable

UNION ALL

SELECT class, section, null as rollnu, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section

UNION ALL

SELECT class, null, null, sum(marks) [sum]
FROM #tempTable
GROUP BY class

UNION ALL

SELECT null, null, null, sum(marks) [sum]
FROM #tempTable

ORDER BY class DESC, section DESC, rollno DESC

 

Thanks, please let me know your thoughts and comments!!!


Advertisements
Categories: SQL Tips Tags: , , ,
  1. March 27, 2015 at 5:41 pm

    GROUP BY … WITH ROLLUP is available in SQL2000 probably in 7.0 too

  1. April 30, 2015 at 2:47 pm
  2. April 30, 2015 at 2:44 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: