SQL Server Pivot Table Example

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

Posted

in

by

Tags:


Related Posts

Comments

9 responses to “SQL Server Pivot Table Example”

  1. Amit Dubey Avatar
    Amit Dubey

    This is one of the best articles I read online. No crap, just useful information.

    Thanks Everyone!!

  2. Florian Avatar
    Florian

    Nice article. Very useful information. Thanks a lot

  3. Vishal Avatar

    if the order_made is column of varchar type which function can i use instead of sum,max or avg

  4. Jeyaganeshcse Avatar
    Jeyaganeshcse

    very nice to post

    1. Pankaj Tiwari Avatar

      What is this Image in reply? Isn’t this inappropriate for this place?

      1. JitendraZaa Avatar
        JitendraZaa

        Removed.. Thanks

  5. P Dineshperumal Avatar
    P Dineshperumal

    can use distinct in pivot query………

  6. jyoti Avatar
    jyoti

    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

  7. mani Avatar
    mani

    i want to display a single row of data in to a column

Leave a Reply to JeyaganeshcseCancel reply

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

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading