Dynamic Group by without Dynamic SQL

Good After All,

I learned some cool SQL magic the other day! I wanted to dynaamiclly drop columns and group the statement in a store procedure without using dynamic sql.  Dropping the unwanted columns was easy, but the group by statement was not that simple to figure out right away.  In tihs example I will count the number of players and change the columns being outputted.

As always let's create the temp tables with data.  Well use the same values we did a few blogs ago to make things easier.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#TempCount') ) DROP TABLE #TempCount


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  




Before we can start diving into the group by we need to set up some variables.  Below are the variables I have as well as defaul the value to 0 if its set to null.

--Declaring the variables
DECLARE @TeamOut BIT
DECLARE @CityOut BITD
ECLARE @StateOut BIT

--assinging values to variables
SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=1

--If values are passed as null then convert to 0
IF ISNULL(@TeamOut,'')          = '' SET @TeamOut=0
IF ISNULL(@CityOut,'')          = '' SET @CityOut=0
IF ISNULL(@StateOut,'')         = '' SET @StateOut=0

Now let's look at the SQL select statement.

SELECT COUNT(p.PlayerID) AS total,
       CASE WHEN @TeamOut = 1 THEN Team ELSE NULL END as Team,
       CASE WHEN @CityOut = 1 THEN City ELSE NULL END as City,
       CASE WHEN @StateOut = 1 THEN State ELSE NULL END as STATE
INTO #TempCount      
FROM #Teams t
INNER JOIN #Roster r ON t.TeamID = r.TeamID
INNER JOIN #Players p ON r.PlayerID = p.PlayerID  
GROUP BY    
   (CASE WHEN @TeamOut=1 THEN Team ELSE NULL END),
   (CASE WHEN @CityOut=1 THEN City ELSE NULL END),
   (CASE WHEN @StateOut=1 THEN State ELSE NULL END)

As you can see I use a case statement with the use of the use of the variables.  This seems to work out perfect.  Now what happens when the bit flag is set to 0 and we dont want to show it?  I thought you would ask that question.  It's pretty simple.  We just modified the temp table (#TempCount) that holds all the data.

--Removing columns we dont want to output
IF @TeamOut         = 0 ALTER TABLE #TempCount DROP COLUMN Team
IF @CityOut         = 0 ALTER TABLE #TempCount DROP COLUMN City
IF @StateOut        = 0 ALTER TABLE #TempCount DROP COLUMN STATE

Now we just do a simple Select statement from the temp table to get the results.  Here are a few different examples with just changing the bit flag.


Example 1.

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=NULL

Example 2:

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=1







Example 3:

SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=NULL

This is just a simple example for you, but you can see the value is does.  Hope this was valueable to you as it was to me.

Have a great day!

Robert