Click here to Skip to main content
15,351,089 members
Articles / Programming Languages / SQL
Posted 29 Nov 2012

Tagged as


89 bookmarked

Simple Way To Use Pivot In SQL Query

Rate me:
Please Sign up or sign in to vote.
4.71/5 (95 votes)
4 Dec 2015CPOL
Transforming data from row-level data to columnar data.


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.


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.

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

Image 1

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

Image 2


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


About the Author

Maksud Saifullah Pulak
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

QuestionPivot sql query in ASP.NET Pin
Member 1450613619-Jun-19 15:40
MemberMember 1450613619-Jun-19 15:40 
AnswerRe: Pivot sql query in ASP.NET Pin
Andy Bantly16-Nov-20 7:27
MemberAndy Bantly16-Nov-20 7:27 
Questionplease guide me query is executing but no data return,please have a look Pin
akhter8617-May-19 15:34
Memberakhter8617-May-19 15:34 
GeneralMy vote of 5 Pin
Anurag Gandhi16-May-19 4:44
professionalAnurag Gandhi16-May-19 4:44 
GeneralMy vote of 5 Pin
Matias Lopez17-Apr-19 4:01
MemberMatias Lopez17-Apr-19 4:01 
QuestionI need help to pivot this Pin
Member 1085260719-Sep-18 4:09
MemberMember 1085260719-Sep-18 4:09 
AnswerRe: I need help to pivot this Pin
Matias Lopez16-Apr-19 19:19
MemberMatias Lopez16-Apr-19 19:19 
QuestionSimple Way To Use Pivot In SQL Query Pin
Member 1373747320-Mar-18 10:50
MemberMember 1373747320-Mar-18 10:50 
QuestionProblem while writing pivot query in data-set for report viewer Pin
skpatil.98714-Jan-18 22:51
Memberskpatil.98714-Jan-18 22:51 
QuestionPivot on Pivot Pin
Manik Manocha10-Jun-17 2:55
MemberManik Manocha10-Jun-17 2:55 
Questionsimple and easy to understand Pin
shamim07820-Dec-16 12:06
Membershamim07820-Dec-16 12:06 
QuestionDead simple Pin
Member 944845428-Jan-16 9:06
MemberMember 944845428-Jan-16 9:06 
GeneralMy vote of 4 Pin
Member 1227488719-Jan-16 18:01
MemberMember 1227488719-Jan-16 18:01 
Questionthanks Pin
alcitect11-Nov-15 23:15
Memberalcitect11-Nov-15 23:15 
QuestionSum for Rows and Column Pin
Member 444291617-Jul-15 3:30
MemberMember 444291617-Jul-15 3:30 
QuestionThanks Pin
Narendra Singh Chauhan16-Jul-15 0:55
MemberNarendra Singh Chauhan16-Jul-15 0:55 
GeneralNicely explained Pin
Member 1171243922-May-15 13:54
MemberMember 1171243922-May-15 13:54 
GeneralGreat Pin
Member 1140761327-Jan-15 20:06
MemberMember 1140761327-Jan-15 20:06 
GeneralIt is important Pin
Md. Sydur Rahman13-Jan-15 17:45
MemberMd. Sydur Rahman13-Jan-15 17:45 
QuestionError? Most Likely This. Pin
netferret12-Dec-14 5:07
Membernetferret12-Dec-14 5:07 
GeneralThanks Pin
Paulo Augusto Kunzel29-Sep-14 3:30
professionalPaulo Augusto Kunzel29-Sep-14 3:30 
QuestionError not sure why Pin
lgmanuel12-Aug-14 10:46
Memberlgmanuel12-Aug-14 10:46 
AnswerRe: Error not sure why Pin
Nickysqlboy26-Aug-14 3:55
MemberNickysqlboy26-Aug-14 3:55 
QuestionPivot Table... Pin
Harnis Findoliya30-May-14 1:41
MemberHarnis Findoliya30-May-14 1:41 
GeneralMy vote of 5 Pin
lnucleus12-May-14 1:44
Memberlnucleus12-May-14 1:44 

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.