This post intends to help TSQL developers get started with PIVOT/CROSS TAB queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.

Let us say for example we need a result set as following

from a table which has data as follows.

This essentially means the task is to generate a tabular result set from the rows that we have. With SQL Server 2000, most of us might have used #TEMP tables where we update the data temporarily and SELECTED from. Another option that most of the developers might have used with SQL Server 2000 is the CASE statement. In the example above, you can use a series of 12 CASE statements which can take for each column.

SQL Server 2005 has a handy PIVOT operator which can help us with the above task. The PIVOT clause generates a resultset as shown in the example above. For the purpose of this example, let us create a table as follows.

CREATE TABLE invoice (
    InvoiceNumber VARCHAR(20),
    invoiceDate DATETIME,
    InvoiceAmount MONEY 
)

The above SQL statement creates a table for the purpose of this example. Now let us insert some data into it.

INSERT INTO invoice
SELECT 'INV001', '2005-01-01', 100 UNION ALL
SELECT 'INV002', '2005-02-01', 40 UNION ALL
SELECT 'INV003', '2005-03-01', 60 UNION ALL
SELECT 'INV004', '2005-03-10', 15 UNION ALL
SELECT 'INV005', '2005-04-01', 50 UNION ALL
SELECT 'INV006', '2005-05-01', 77 UNION ALL
SELECT 'INV007', '2005-06-01', 12 UNION ALL
SELECT 'INV008', '2005-06-05', 56 UNION ALL
SELECT 'INV009', '2005-07-01', 34 UNION ALL
SELECT 'INV010', '2005-08-01', 76 UNION ALL
SELECT 'INV011', '2005-09-01', 24 UNION ALL
SELECT 'INV012', '2005-09-20', 10 UNION ALL
SELECT 'INV013', '2005-10-01', 15 UNION ALL
SELECT 'INV014', '2005-11-01', 40 UNION ALL
SELECT 'INV015', '2005-11-15', 21 UNION ALL
SELECT 'INV016', '2005-12-01', 17 UNION ALL
SELECT 'INV017', '2006-01-01', 34 UNION ALL
SELECT 'INV018', '2006-02-01', 24 UNION ALL
SELECT 'INV019', '2006-03-01', 56 UNION ALL
SELECT 'INV020', '2006-03-10', 43 UNION ALL
SELECT 'INV021', '2006-04-01', 24 UNION ALL
SELECT 'INV022', '2006-05-01', 11 UNION ALL
SELECT 'INV023', '2006-06-01', 6 UNION ALL
SELECT 'INV024', '2006-06-05', 13

Let us create the PIVOT query now which will return a result set as per what we needed.

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount) FOR [month] IN (
        jan, feb, mar, apr, 
        may, jun, jul, aug, sep, oct, nov, dec
    )
)AS p

The above query generates a result set that we needed. Let me explain the structure of the query. The first part of the query

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s 

The first part of the query generates a subtable (view) as per the following.

The second part of the query generates a PIVOT table from the above result set.

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year], 
        left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS p

The above query returns a result set as follows.

Hope this helps! If you found this useful or have a question, please post a comment to let me know about it.