RollUP function in conjunction with IIF and CHOOSE function of DENALI

Introduction

In SQL Server, if we want to perform the Total and Subtotals inorder to gerenate the reports, we can take the help of the RollUP function.In this short article, we will see the usage of the same in conjunction with IIF and CHOOSE function of DENALI

Using the code

Suppose we have the below table

--Declare the sample table
DECLARE @T TABLE(ObjectType VARCHAR(50), ObjectDescription VARCHAR(50))

--Populate some data
INSERT INTO @T VALUES
	('Stored Procedure', 'Something about the first Stored Procedure')
	,('Stored Procedure', 'Something about the second Stored Procedure')
	,('Synonym', 'Something about the first Synonym')
	,('Stored Procedure', 'Something about the third Stored Procedure')
	,('Table', 'Something about the first Table')
	,('Table', 'Something about the second Table')
	,('View', 'Something about the first View')
	,('Trigger', 'Something about the first Trigger')
	,('Synonym', 'Something about the second Synonym')
	,('Table', 'Something about the third Table')
	,('Stored Procedure', 'Something about the fourth Stored Procedure')
	,('Function', 'Something about the first User Define Function')

--Display the record
SELECT * FROM @T

/*
Output
--------

ObjectType		ObjectDescription
----------------	--------------------------------------------
Stored Procedure	Something about the first Stored Procedure
Stored Procedure	Something about the second Stored Procedure
Synonym			Something about the first Synonym
Stored Procedure	Something about the third Stored Procedure
Table			Something about the first Table
Table			Something about the second Table
View			Something about the first View
Trigger			Something about the first Trigger
Synonym			Something about the second Synonym
Table			Something about the third Table
Stored Procedure	Something about the fourth Stored Procedure
Function		Something about the first User Define Function

*/

Let's say our expected output should be similar to as under

That means , for every "ObjectType" there will be an individual count of that, then a Subtotalling of that and finally to present the total number of objects.

Solution 1 (Using Case Statement):

SELECT ObjectType = 
		CASE WHEN t.ObjectType IS NULL AND t.ObjectDescription IS NULL THEN 'Total'
		     WHEN t.ObjectDescription IS NULL THEN t.ObjectType + ' Count'
		ELSE t.ObjectType END
	   ,ObjectDescription = ISNULL(t.ObjectDescription, '')
	   ,ObjectCount = COUNT(t.ObjectType)
FROM   @T t
GROUP BY ROLLUP(ObjectType,ObjectDescription)

Explanation

In the first CASE statement, we are checking whenever both the "ObjectType" and "ObjectDescription" fields are NULL (which will appear at the end of teh resultset), then it is the situation for "Totaling". If only the "ObjectDescription" field is NULL , then it is the situation for "Sub-Totaling".

Solution 2 (Using IIF Statement of DENALI):

SELECT ObjectType = IIF(t.ObjectType IS NULL AND t.ObjectDescription IS NULL,
			'Total',
			IIF(t.ObjectDescription IS NULL,
			    t.ObjectType + ' Count',
			    t.ObjectType
			    ) --end inner IIF
			)--end outer IIF
					
	   ,ObjectDescription = ISNULL(t.ObjectDescription, '')
	   ,ObjectCount = COUNT(t.ObjectType)
FROM   @T t
GROUP BY ROLLUP(ObjectType,ObjectDescription)

Explanation

It follows the same explanation as the above; the only change is that it has a nested IIF() function of Denali.For more about IIF function you can further read my earlier article at Let's learn IIF Function in Sql Server 2012

Solution 3 (Using CHOOSE Statement of DENALI):

SELECT ObjectType = 
		CHOOSE( CASE WHEN t.ObjectType IS NULL AND t.ObjectDescription IS NULL THEN 1 
			     ELSE 2 
		        END
				,'Total'							
				,CHOOSE( CASE WHEN t.ObjectDescription IS NULL THEN 1 ELSE 2 END,t.ObjectType + ' Count',t.ObjectType)
			  )
	   ,ObjectDescription = ISNULL(t.ObjectDescription, '')
	   ,ObjectCount = COUNT(t.ObjectType)
FROM   @T t
GROUP BY ROLLUP(ObjectType,ObjectDescription)

Explanation

Nothing much to say about the explanation only thing is that the way we are setting the index position of the CHOOSE statement at runtime.The general syntax of CHOOSE function is as under

For more about CHOOSE function you can further read my earlier article at Let's learn Choose Function in Sql Server 2012

References

Summarizing Data Using ROLLUP

Conclusion

Hope this article will be useful and also it shows the multiple ways of solving the same problem statement.The experimental file is attached herewith.Thanks for your time to read the article.

N.B.~This article is not meant for a source of benchmarking among the 3 methods shown above.Interested readers are, however, welcome to perform a benchmark test and can submit the result and with due credit to that user and permission of web master, I will add the same in the article.