Get articles everyday as a email directly to your inbox:

Click to publicize, if you like this article :

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 Output