New with SQL Server 2005 are some really cool T-SQL enhancements.
One that I was really looking forward to sinking my teeth into was the PIVOT/UNPIVOT enhancements to the Select statement. That was until I started to use it.
After a little frustration I figured out the PIVOT's idiosyncrasies and was pretty disappointed in some basic functionality that is missing.
First, let’s talk about “how” to use it in the most basic fashion.
Using your select statement, you create the query as you typically would, then you add the following to it.
SQL 2000
SELECT GroupByColumn1,GroupByColumn2,
Max(
CASE
when PivotColumn = ‘abc’ then PivotColumn else null
when PivotColumn = ‘def’ then PivotColumn else null
END
)
FROM myTable
GROUP BY groupByColumn1,GroupByColumn2
SQL 2005
SELECT * FROM myTable
PIVOT ((Aggregate function) FOR Column Name IN (List of Values))
AS AliasName
Seems pretty basic right?? (Not really)
The Aggregate function replaces your normal aggregate wrapper for the case statement, and the List of values are the values you would use within the case statement. The problem (and the missing functionality) is in the List of Values.
You would think given that the values MUST be convertible to NVarchar that a string is what is being called for. And strings are typically quoted like this ‘abcdefg’. Well, not so with this option.
Something like this will not work.
PIVOT (MAX(value) FOR myColumn IN ('11/18/2005','11/19/2005','11/20/2005') )
AS myAlias
The SQL Parser wants to see a “raw” or bracketed values from the result set. So you MUST do something like this.
PIVOT (MAX(value) FOR myColumn IN ([11/18/2005],[11/19/2005],[11/20/2005]) )
AS myAlias
Or
PIVOT (MAX(value) FOR myColumn IN (abc,def,hij) )
AS myAlias
Here’s where the missing functionality comes in. >>> A sub query will not work in the place of the column list. The following will not work.
PIVOT (MAX(value) FOR myColumn IN (SELECT myPivotcolumn FROM MyTable) )
AS myAlias
As I was working with this, I thought.. You have got to be kidding. Is this really a limitation? I tried every which way I could to make this work.. If anyone can prove me wrong, PLEASE do so…
So, to get around this, I do something like this. For all the folks that are going to throw stones about the dynamic SQL, I don’t like doing dynamic SQL either… But I don’t see a way around it in this case.
DECLARE @Dates varchar(2000)
SET @dates = ''
--// Get a list of the pivot columns that are important to you.
SELECT @Dates = @Dates + '[' + convert(varchar(12),CaptureDate,101) + '],'
FROM (SELECT Distinct CaptureDate FROM DurationSummary) Duration
--// Remove the trailing comma
SET @Dates = LEFT(@Dates, LEN(@Dates) - 1)
--// Now execute the Select with the PIVOT and dynamically add the list
--// of dates for the columns
EXEC( 'SELECT * FROM DurationSummary PIVOT (MAX(Quantity) FOR CaptureDate IN (' + @Dates + ')) AS X' )
Merry Christmas / Happy Holidays & a VERY Happy New Year!!!
Bill