SQL Tip #5 – PIVOT Table

We write sql queries in order to get different result sets like full, partial, calculated, grouped, sorted etc from the database tables. However sometimes we have requirements that we have to rotate our tables. Sounds confusing?

Let’s keep it simple and consider the following two screen grabs.

SQL Table:

Expected Results:

Wow, that’s look like a lot of work! That is a combination of tricky sql, temporary tables, loops, aggregation……, blah blah blah

Don’t worry let’s keep it simple, stupid(KISS).

MS SQL Server 2005 and above has a function called PIVOT. It s very simple to use and powerful. With the help of this function we will be able to rotate sql tables and result sets.

Simple steps to make it happen:

  1. Identify all the columns those will be part of the desired result set.
  2. Find the column on which we will apply aggregation(sum,ave,max,min etc)
  3. Identify the column which values will be the column header.
  4. Specify the column values mentioned in step3 with comma separated and surrounded by square brackets.

So, if we now follow above four steps and extract information from the above sales table, it will be as below:

  1. Year, Month, SalesAmount
  2. SalesAmount
  3. Month
  4. [Jan],[Feb] ,[Mar] …. etc

We are nearly there if all the above steps made sense to you so far.

Now we have all the information we need. All we have to do now is to fill the below template with required information.
Template:

Our SQL query should look like below:

SELECT *
FROM
(
    SELECT SalesYear, SalesMonth,Amount
    FROM Sales
 ) AS SourceTable
PIVOT
(
       SUM(Amount )
       FOR SalesMonth
       IN ( [Jan],[Feb] ,[Mar],
            [Apr],[May],[Jun] ,[Jul],
            [Aug],[Sep] ,[Oct],[Nov] ,[Dec])
) AS PivotTable;

In the above query we have hard coded the column names. Well it’s not fun when you have to specify a number of columns.

However, there is a work arround as follows:

DECLARE @columnList nvarchar (MAX)
DECLARE @pivotsql nvarchar (MAX)

SELECT @columnList =   STUFF(
             (
               SELECT ',' + '[' + SalesMonth + ']'
               FROM Sales
               GROUP BY SalesMonth
               FOR XML PATH( '')
           )
        ,1, 1,'' )

SET @pivotsql =
       N'SELECT *
      FROM
      (
            SELECT SalesYear, SalesMonth,Amount
            FROM Sales
       ) AS SourceTable
      PIVOT
      (
           SUM(Amount )
           FOR SalesMonth
           IN ( ' + @columnList +' )
      ) AS PivotTable;'

EXEC sp_executesql @pivotsql

Hopefully this tutorial will be a help to someone somewhere.
Enjoy coding.

Advertisements

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