Using T-SQL PIVOT Clause

 

One feature in SQL Server 2005 that made my life easier many occasions is PIVOT clause in T-SQL. You will find it useful if you need to generate Cross-Tab summaries especially while generating reports. 

 

This feature allows you to convert a column’s values in table rows into columns. To see how it works, look at the following example

 

–Sales table rows

 

Select * from Sales

 

salesId     salesYear salesProduct salesQuantity

———– ——— ———— ————-

1           2004      Camera       100

2           2004      Memory       200

3           2004      HDD          1000

4           2005      Camera       150

5           2005      Memory       1200

6           2005      HDD          100

7           2006      Camera       300

8           2006      Memory       200

9           2006      HDD          400

 

 

With SQL Server 2000, a cross-tab summary to list sales-quantity-by-month can be generated by a query like this:

 

–Generate CrossTab (SQL Server 2000 style)

Select salesYear,

         Sum(case when salesProduct = ‘Camera’

                        then salesQuantity

                        else 0

                        end

              ) as ‘Camera’,

         Sum(case when salesProduct = ‘Memory’

                        then salesQuantity

                        else 0

                        end

              ) as ‘Memory’,

         Sum(case when salesProduct = ‘HDD’

                        then salesQuantity

                        else 0

                        end

              ) as ‘HDD’

From Sales

Group By salesYear

 

And you get result below:

 

salesYear Camera      Memory      HDD

——— ———– ———– ———–

2004      100         200         1000

2005      150         1200        100

2006      300         200         400

 

 

Using PIVOT

 

 

In SQL Server 2005, the above cross tab result be generated using the PIVOT clause which makes the query simple as shown below.

 

–SQL 2005, Use Pivot clause to get the cross tab summary

SELECT salesYear, [Camera],[Memory],[HDD]

FROM (SELECT salesYear, salesQuantity, salesProduct FROM Sales ) AS ProductSales

PIVOT (SUM(salesQuantity)

FOR salesProduct IN ([Camera],[Memory],[HDD])) AS SamplePivot

 

 

Output:

 

salesYear Camera      Memory      HDD

——— ———– ———– ———–

2004      100         200         1000

2005      150         1200        100

2006      300         200         400

 

 

Note that the results are grouped by ‘salesYear’ eventhough it is not a part of PIVOT statement. Check the result by removing the ‘salesYear’ from the query as follows:

 

SELECT [Camera],[Memory],[HDD]

FROM (SELECT salesQuantity, salesProduct FROM Sales ) AS ProductSales

PIVOT (SUM(salesQuantity)

FOR salesProduct IN ([Camera],[Memory],[HDD])) AS SamplePivot

 

Output:

 

Camera      Memory      HDD

———– ———– ———–

550         1600        1500

 

 

Advertisements

4 thoughts on “Using T-SQL PIVOT Clause

  1. One way to achieve this is to use CLR stored procedures. It gives you some flexibility where you can build a string that becomes the dynamic column headers in the cross-tab. Hope this helps

    -Biju

  2. It’s the first time I commented here and I must say that you share genuine, and quality information for other bloggers! Good job.
    p.s. You have a very good template . Where did you find it?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s