SQL Server Pivot Table Example

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 9 Comments on SQL Server Pivot Table Example

Example and tutorial of using PIVOT in SQL Server

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and understandability of mined data, pivot tables are common and preferred widely.

Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.
But the table columns are formed by the report data itself.

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to t-sql with the release of MS SQL Server 2005.

In MS SQL Server 2008, we can still use the PIVOT command and UNPIVOT command to build and use pivot tables in sql.
T-SQL Pivot and Unpivot statements will transform and rotate a tabular data into an other table value data in sql .
Since Pivot / Unpivot are SQL2005 t-sql enhancements, databases which you want to execute pivot and unpivot commands should be at least at compatibility level 90 (SQL2005) or 100 (SQL2008).

T-SQL Pivot Syntax

SELECT
  [non-pivoted column], -- optional
  [additional non-pivoted columns], -- optional
  [first pivoted column],-- optional
 [additional pivoted columns]
FROM (
 SELECT query producing sql data for pivot
 -- select pivot columns as dimensions and
 -- value columns as measures from sql tables
) AS TableAlias
PIVOT
(
 <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc
 FOR [<column name containing values for pivot table columns>]
 IN (
 [first pivoted column], ..., [last pivoted column]
 )
) AS PivotTableAlias
ORDER BY clause -- optional

Example SQL server 2005:

create table order_rep (iteNag varchar(10), mnth int, year int, ordermade int)

insert into order_rep values('Shiva',1,2009, 5)
insert into order_rep values('Shiva',2,2009, 6)
insert into order_rep values('Shiva',1,2009, 10)
insert into order_rep values('Shiva',4,2009, 5)
insert into order_rep values('Shiva',5,2009, 7)
insert into order_rep values('Shiva',2,2009, 5)
insert into order_rep values('Shiva',1,2009, 4)
insert into order_rep values('Shiva',6,2009, 15)
insert into order_rep values('Shiva',8,2009, 8 )
insert into order_rep values('Shiva',3,2010, 5)
insert into order_rep values('Shiva',5,2010, 7)
insert into order_rep values('Shiva',12,2010, 5)
insert into order_rep values('Shiva',11,2010, 4)
insert into order_rep values('Shiva',1,2010, 7)
insert into order_rep values('Shiva',5,2010, 5)

insert into order_rep values('Soft',2,2009, 6)
insert into order_rep values('Soft',4,2009, 7)
insert into order_rep values('Soft',2,2009, 4)
insert into order_rep values('Soft',3,2009, 5)
insert into order_rep values('Soft',5,2009, 7)
insert into order_rep values('Soft',12,2009, 12)
insert into order_rep values('Soft',11,2009, 4)
insert into order_rep values('Soft',1,2009, 9)
insert into order_rep values('Soft',5,2009, 4)
insert into order_rep values('Soft',3,2009, 5)
insert into order_rep values('Soft',4,2010, 7)
insert into order_rep values('Soft',1,2010, 1)
insert into order_rep values('Soft',4,2010, 4)
insert into order_rep values('Soft',2,2010, 9)
insert into order_rep values('Soft',5,2010, 4)

insert into order_rep values('Nag',1,2009, 5)
insert into order_rep values('Nag',3,2009, 6)
insert into order_rep values('Nag',5,2009, 8 )
insert into order_rep values('Nag',12,2009, 23)
insert into order_rep values('Nag',9,2009, 45)
insert into order_rep values('Nag',5,2009, 3)
insert into order_rep values('Nag',1,2009, 5)
insert into order_rep values('Nag',4,2009, 3)
insert into order_rep values('Nag',3,2009, 9)
insert into order_rep values('Nag',3,2010, 5)
insert into order_rep values('Nag',5,2010, 7)
insert into order_rep values('Nag',12,2010, 12)
insert into order_rep values('Nag',11,2010, 4)
insert into order_rep values('Nag',1,2010, 9)
insert into order_rep values('Nag',5,2010, 4)

Now the script using Pivot table is:

SELECT *
FROM (
SELECT IteNag
        , cast(Year as varchar(4)) + ' ' + CONVERT(varchar(3), dateadd(m, mnth, -1), 107) as MnthName
        , OrderMade
FROM Order_rep
) P
PIVOT (
SUM(OrderMade)
FOR MnthName IN ([2009 Apr], [2009 May], [2009 Jun], [2009 Jul], [2009 Aug])
) AS PVT
drop table order_rep

Output:

SQL Server Pivot Table
SQL Server Pivot Table Output

Related posts

9 thoughts on “SQL Server Pivot Table Example”

  1. thanks a lot

    but i need one more help if i have data in my table as per booking date

    i want to pivot it like per year with every month

    so i have already achieved it using this article

    but i want to order data by yearly

    so is it possible to use order by clause in base query

    thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.