Using Dynamic Values in the PIVOT Operator

21 February, 2009 (10:20) | T-SQL | By: Mark V

I recently worked on a report for a client in which a date input parameter was used to determine the end dates of the previous four calendar quarters. Each time the report ran, it returned data for all four of those quarters.

I ended up using PIVOT in the course of the stored procedure for the report. The first time I did so, I hard-coded the four quarter end dates I needed to work with. This worked fine for my testing, but I needed the PIVOT to use whatever four quarters end dates were derived from the input parameter. I was fortunate that I knew ahead of time that I would always be pivoting on four and only four date values.

With that consistency in mind, I was able to create a common tables expression with a derived column to consistently “alias” the first quarter as 1, the second quarter as 2, and so forth.

Here is the breakdown of how this works using data on the dining habits of Hobbits. In this example, the report involved requires looking at the first three days of the month. This provides the consistency required to make this particular technique work. It could be possible to modify this procedure to work on value with less consistency but I have not yet tried it.

-- Create the Hobbit table to house the meal data

CREATE TABLE Hobbit

(

        Hobbit varchar(20)

      , MealDate date

      , MealCount int

)

 

-- Populate Hobbit table with meal data

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-01', 4)

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-02', 5)

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-03', 3)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-01', 6)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-02', 7)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-03', 3)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-01', 4)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-02', 8)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-03', 7)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-01', 4)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-02', 8)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-03', 7)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-01', 6)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-02', 9)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-03', 12)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-01', 4)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-02', 8)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-03', 7)

 

-- Pivot with hard coded January values

SELECT

        Hobbit

      , [2009-01-01]

      , [2009-01-02]

      , [2009-01-03]

FROM 

(

SELECT 

        Hobbit

      , MealDate

      , MealCount

FROM Hobbit) h

PIVOT

(

SUM(MealCount)

FOR MealDate IN

(

        [2009-01-01]

      , [2009-01-02]

      , [2009-01-03]

)

) AS pvt

 

Results of January hard coded PIVOT:

 

Hobbit               2009-01-01  2009-01-02  2009-01-03

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

-- Pivot with hard coded February values

SELECT

        Hobbit

      , [2009-02-01]

      , [2009-02-02]

      , [2009-02-03]

FROM 

(

SELECT 

        Hobbit

      , MealDate

      , MealCount

FROM Hobbit) h

PIVOT

(

SUM(MealCount)

FOR MealDate IN

(

        [2009-02-01]

      , [2009-02-02]

      , [2009-02-03]

)

) AS pvt

 

Results of February hard coded PIVOT:

 

Hobbit               2009-02-01  2009-02-02  2009-02-03

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

As you can see from this pivot tables above, Hobbits eat often. The hard coded date values form the column headers. This is nice and all but requires a code change to report on a different set of dates.

Here we see how you can make a CTE with a derived column to pass in to the PIVOT operator. This allows the actual PIVOT to remain exactly the same while reporting on different dates.

-- Varbiable to hold the month number on which to PIVOT

DECLARE @MealMonth int

 

SET @MealMonth = 1 -- Using 1 to prepresent January

 

-- Common table expression to derive MealDay column

;

WITH HobbitMealDays AS

(

SELECT

        Hobbit

      , MealDate

      , DAY(MealDate) AS MealDay -- Using day datepart as integer value

      , MealCount

FROM Hobbit

WHERE MONTH(MealDate) = @MealMonth

)

 

SELECT

        Hobbit

      , [1] AS Day1 -- Alias to stand in for hard coded column headers

      , [2] AS Day2

      , [3] AS Day3

FROM 

(

SELECT 

        Hobbit

      , MealDay

      , MealCount

FROM HobbitMealDays) h

PIVOT

(

SUM(MealCount)

FOR MealDay IN

(

        [1] -- these values are known possible values of MealDay derived column

      , [2]

      , [3]

)

) AS pvt

 

Results of dynamic using @Mealmonth = 1 to represent January in PIVOT:

 

Hobbit               Day1        Day2        Day3

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

Just to make the comparison easier, here are the hard coded January PIVOT results again.

Results of January hard coded PIVOT:

 

Hobbit               2009-01-01  2009-01-02  2009-01-03

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

If you set @MealMonth = 2 and run the PIVOT again, you will get the February results:

Results of dynamic using @Mealmonth = 2 to represent February in PIVOT:

 

 

Hobbit               Day1        Day2        Day3

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

Again, for ease of comparison, here are the hard coded February results again.

Results of February hard coded PIVOT:

 

Hobbit               2009-02-01  2009-02-02  2009-02-03

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

-- Cleaning up

DROP TABLE Hobbit

GO

 

As you can see above, the “dynamic” PIVOT returns the exact same values as the hard coded versions but offers the flexibility of reporting on different periods without changing code. You could just reference a parameter in the CTE instead of a variable as I did.

Have fun.

Write a comment