SQL Server 2005 - Pivot Query

I suppose most of us might have come across the following situation 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

SELECT 'INV002', '2005-02-01', 40 UNION

SELECT 'INV003', '2005-03-01', 60 UNION

SELECT 'INV004', '2005-03-10', 15 UNION

SELECT 'INV005', '2005-04-01', 50 UNION

SELECT 'INV006', '2005-05-01', 77 UNION

SELECT 'INV007', '2005-06-01', 12 UNION

SELECT 'INV008', '2005-06-05', 56 UNION

SELECT 'INV009', '2005-07-01', 34 UNION

SELECT 'INV010', '2005-08-01', 76 UNION

SELECT 'INV011', '2005-09-01', 24 UNION

SELECT 'INV012', '2005-09-20', 10 UNION

SELECT 'INV013', '2005-10-01', 15 UNION

SELECT 'INV014', '2005-11-01', 40 UNION

SELECT 'INV015', '2005-11-15', 21 UNION

SELECT 'INV016', '2005-12-01', 17 UNION

SELECT 'INV017', '2006-01-01', 34 UNION

SELECT 'INV018', '2006-02-01', 24 UNION

SELECT 'INV019', '2006-03-01', 56 UNION

SELECT 'INV020', '2006-03-10', 43 UNION

SELECT 'INV021', '2006-04-01', 24 UNION

SELECT 'INV022', '2006-05-01', 11 UNION

SELECT 'INV023', '2006-06-01', 6 UNION

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 comnment to let me know about it.