Click here to Skip to main content
15,868,116 members
Articles / Programming Languages / SQL
Tip/Trick

Simple Way To Use Pivot In SQL Query

Rate me:
Please Sign up or sign in to vote.
4.73/5 (96 votes)
4 Dec 2015CPOL 1.6M   89   59
Transforming data from row-level data to columnar data.

Introduction

This is a very simple example of Pivot query for the beginners. We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

Background

This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past. 

Using the Code

Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate, InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum of InvoiceAmount each month.

SQL
SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult 

Image 1

SQL
SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt

Image 2

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Bangladesh Bangladesh
Maksud Saifullah Pulak is a Software Engineer. He have been working more than 5 years of professional experience in Software Development industry. Currently he is working with project management and development of several software projects from his country.Technical expertise ASP.NET, C#, .NET,Visual Studio, Sql Server,Data Structure.

Comments and Discussions

 
GeneralMy vote of 5 Pin
lnucleus12-May-14 1:44
lnucleus12-May-14 1:44 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak18-May-14 1:50
Maksud Saifullah Pulak18-May-14 1:50 
GeneralMy vote of 5 Pin
Animesh Datta4-May-14 22:51
Animesh Datta4-May-14 22:51 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak12-May-14 1:02
Maksud Saifullah Pulak12-May-14 1:02 
GeneralMy vote of 2 Pin
Pawan Mishra14-Apr-14 2:58
Pawan Mishra14-Apr-14 2:58 
GeneralMy vote of 1 Pin
Karthik Chintala26-Mar-14 23:08
Karthik Chintala26-Mar-14 23:08 
BugAvoid keyword PIVOT Pin
Kip Bryan5-Feb-14 7:04
Kip Bryan5-Feb-14 7:04 
GeneralRe: Avoid keyword PIVOT Pin
Maksud Saifullah Pulak3-Mar-14 3:48
Maksud Saifullah Pulak3-Mar-14 3:48 
Thanks for the comment.
Questionexplain me Pin
imtutul11-Jan-14 6:29
imtutul11-Jan-14 6:29 
QuestionDynamic columns Pin
Greesemonkey310-Dec-13 7:37
Greesemonkey310-Dec-13 7:37 
AnswerRe: Dynamic columns Pin
Herman<T>.Instance2-Apr-15 0:26
Herman<T>.Instance2-Apr-15 0:26 
GeneralMy vote of 4 Pin
dexter.kumar7-Oct-13 0:50
dexter.kumar7-Oct-13 0:50 
GeneralRe: My vote of 4 Pin
Maksud Saifullah Pulak29-Nov-13 23:23
Maksud Saifullah Pulak29-Nov-13 23:23 
GeneralMy vote of 3 Pin
Member 101968783-Oct-13 19:01
Member 101968783-Oct-13 19:01 
GeneralRe: My vote of 3 Pin
Maksud Saifullah Pulak5-Oct-13 7:49
Maksud Saifullah Pulak5-Oct-13 7:49 
GeneralMy vote of 1 Pin
pavan bhavsar22-Sep-13 7:09
pavan bhavsar22-Sep-13 7:09 
GeneralMy vote of 5 Pin
rishadek12-Sep-13 1:54
rishadek12-Sep-13 1:54 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak19-Sep-13 22:38
Maksud Saifullah Pulak19-Sep-13 22:38 
GeneralMy vote of 5 Pin
shreeniwas kushwah22-Aug-13 19:45
shreeniwas kushwah22-Aug-13 19:45 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak25-Aug-13 22:27
Maksud Saifullah Pulak25-Aug-13 22:27 
QuestionMy doubt Pin
daranee7311-Aug-13 23:32
daranee7311-Aug-13 23:32 
GeneralMy vote of 5 Pin
MohamedKamalPharm19-Jun-13 21:02
MohamedKamalPharm19-Jun-13 21:02 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak19-Jun-13 21:39
Maksud Saifullah Pulak19-Jun-13 21:39 
GeneralMy vote of 5 Pin
Asif Md. Akhlak30-Nov-12 20:58
Asif Md. Akhlak30-Nov-12 20:58 
GeneralRe: My vote of 5 Pin
Maksud Saifullah Pulak30-Nov-12 21:03
Maksud Saifullah Pulak30-Nov-12 21:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.