In my last walkthrough we talked about how you could pivot a column into a comma delimited string using the COALESCE function. In this walkthrough we’re going to combine that bit of code with the T-SQL PIVOT function and a little bit of dynamic sql to achieve “dynamic columns”. In this example I’m using SQL Server 2008R2, but I think it should work on any version that includes the PIVOT function which I believe was introduced in SQL Server 2005 if I remember correctly. So let’s set up a scenario!

I have a table called “TimesheetHours” that has the following columns:

1. TimesheetDay (DateTime) – Contains the date the hours were worked.

2. Hours (Int) – Contains the amount of hours worked on that day.

Writing a query that retrieves the total hours for each day for a given date range is fairly simple and would look something like this:

--declare and set variables

DECLARE 
@v_StartDate DATETIME = '2011-11-01',
@v_EndDate DATETIME = '2011-11-03'

--select results
SELECT
th.TimesheetDay,
SUM(th.[Hours]) 'Hours'
FROM
TimesheetHours th
WHERE
th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate
GROUP BY
th.TimesheetDay

The result set for the above query would look like this:

Suppose we wanted to show the date as the column header AND have the number of columns increase or decrease based on the number of days included in the range so that our result set looks like this:

To pivot the data into this layout, we can use the T-SQL pivot function. The query to pivot our data into the format shown in the image above would look like this:

DECLARE 
@v_StartDate DATETIME = '2011-11-01',
@v_EndDate DATETIME = '2011-11-03'

SELECT
*
FROM
(
SELECT
th.TimesheetDay,
th.[Hours]
FROM
TimesheetHours th
WHERE
th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate
) src
PIVOT
(
SUM(src.[Hours])
FOR src.TimesheetDay IN ([2011/11/01],[2011/11/02],[2011/11/03])
) AS PivotedView

The query above is static in the number of columns it shows however. I’ve highlighted the section of the code that contains the column names in green above. There are a few things we still need to do in order to make our column dynamic.

1. We need to put together a comma separated list of the columns/days we want to show, based on the values of @v_StartDate and @v_EndDate.

2. We then need to construct the rest of the query using dynamic SQL so we can insert our list from step one into the body of the query.

In order to get a list of columns we want to show, we can use the COALESCE script we covered in my previous post. The code to do that would look like this:

DECLARE 
@v_Columns VARCHAR(MAX),
@v_StartDate DATETIME = '2011-11-01',
@v_EndDate DATETIME = '2011-11-03'
 
--pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') + convert(varchar, TimesheetDay, 111) + '],[' 
FROM 
(SELECT DISTINCT TimesheetDay FROM TimesheetHours) th
WHERE
th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

--delete last two chars of string (the ending ',[')
SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)

--show value

SELECT @v_Columns

The value of @v_Columns would be ‘[2011/11/01],[2011/11/02],[2011/11/03]’ for the given values of @v_StartDate and @v_EndDate in the above query. Now all we really need to do is convert our original pivot query into dynamic SQL and concatenate the value(s) of @v_Columns in the appropriate spot. All together the full query looks like this:

DECLARE 
@v_Columns VARCHAR(MAX),
@v_StartDate DATETIME = '2011-11-01',
@v_EndDate DATETIME = '2011-11-05',
@v_Query VARCHAR(MAX)

--pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') + convert(varchar, TimesheetDay, 111) + '],[' 
FROM 
(SELECT DISTINCT TimesheetDay FROM TimesheetHours) th
WHERE
th.TimesheetDay BETWEEN @v_StartDate AND @v_EndDate

--delete last two chars of string (the ending ',[')

SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)

--construct sql statement

SET @v_Query = 

'SELECT
*
FROM
(
SELECT 
CONVERT(VARCHAR(50), th.TimesheetDay, 111) AS TimesheetDay,
th.[Hours]
FROM
TimesheetHours th
WHERE
th.TimesheetDay BETWEEN ''' + CONVERT(VARCHAR(50), @v_StartDate, 111) + '''
AND ''' + CONVERT(VARCHAR(50), @v_EndDate, 111) + '''
) src
PIVOT
(
SUM(src.[Hours])
FOR src.TimesheetDay IN (' + @v_Columns + ')
) AS pivotview'

--execute sql statement

EXEC(@v_Query)

Not exactly the easiest code to read, but such is the “beauty” of dynamic sql (let’s not even get started on the nightmare debugging or security implications of dynamic SQL.). We do however now have a query we can run for multiple date ranges, and it will produce dynamic columns.

As an example if we run the query with the following values:

@v_StartDate DATETIME = '2011-11-01',

@v_EndDate DATETIME = '2011-11-03',

Our results will look like:

<!--[endif]-->

But if we ran the query with a date range that includes 5 days like this

@v_StartDate DATETIME = '2011-11-01',

@v_EndDate DATETIME = '2011-11-05',

Our results will look like this:

Well I think that wraps it up for this session, go forth and pivot.