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.
Mark Vaillancourt 