Rotating Header Image

Pivot…. I said PIVOT!!!!

Apparently I must of skipped this entry in BOL when I was going through and memorizing it LOL… I had a “brain fart” yesterday when I tried to use the PIVOT function and I just couldn't get the thing to work. I had used it in the past and thought maybe it was just late in the day and I was missing something in my format of my code. As it ends up it was nothing to do with my code and EVERYTHING to do with the compatibility level of my database (80). PIVOT is only available for databases that are at 90 compatibility mode or higher.

“When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.”

Here is a good example (taken from Jerry Dixon's blog about the same subject) of how to do a PIVOT if you're in 80 (SQL2000) compatibility mode or 90/100 (SQL2005/SQL2008) compatibility mode.

SQL 2000

SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002

SQL 2005+

SELECT
    
[1] AS 'January'
    
,[2] AS 'February'
    
,[3] AS 'March'
    
,[4] AS 'April'
    
,[5] AS 'May'
    
,[6] AS 'June'
    
,[7] AS 'July'
    
,[8] AS 'August'
    
,[9] AS 'September'
    
,[10] AS 'October'
    
,[11] AS 'November'
    
,[12] AS 'December'
FROM (
    
SELECT
              
MONTH(OrderDate)     AS 'MonthNumber'
              
,OrderID
          
FROM Sales.SalesOrderHeader
          
WHERE YEAR(OrderDate) = 2002
    
) AS Data
PIVOT
(
          
COUNT(OrderID)
          
FOR MonthNumber IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
     )
AS PVT



Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

5 Comments

  1. Srinivas says:

    PIVOT itself aggregate, Not sure why you are doing by hand

    SELECT
    [1] AS ‘January’
    ,[2] AS ‘February’
    ,[3] AS ‘March’
    ,[4] AS ‘April’
    ,[5] AS ‘May’
    ,[6] AS ‘June’
    ,[7] AS ‘July’
    ,[8] AS ‘August’
    ,[9] AS ‘September’
    ,[10] AS ‘October’
    ,[11] AS ‘November’
    ,[12] AS ‘December’
    FROM (
    SELECT
    MONTH(OrderDate) AS ‘MonthNumber’,
    OrderID
    FROM Sales.SalesOrderHeader
    WHERE YEAR(OrderDate) = 2002

    ) AS Data
    PIVOT(
    COUNT(OrderID)
    FOR MonthNumber IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS PVT

  2. Srinivas

    great catch… the example I pulled does do more work “by hand” then is required. Post Updated and thanks!!

  3. […] Doing Pivot Tables in SQL Server – if your data is organized by row, and you need it in columns instead, then you need a pivot table.  Colin gives an example. […]

  4. I’ve come to like my Apple Watch more… I use it enough in my life for things…I enjoy using it and having it. And when I don’t use it, I don’t even know it’s there

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.