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:

SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
	 (class, section, rollno)
	,(class, section)
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

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


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


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


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

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:44 pm
  2. April 30, 2015 at 2:47 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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: