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.
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:
- Identify all the columns those will be part of the desired result set.
- Find the column on which we will apply aggregation(sum,ave,max,min etc)
- Identify the column which values will be the column header.
- 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:
- Year, Month, SalesAmount
- [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.
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.