Click here to Skip to main content
Click here to Skip to main content

Tagged as

Simple Way To Use Pivot In SQL Query

, 29 Nov 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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

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 pivot

License

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

Share

About the Author

Maksud Saifullah Pulak
Software Developer
Bangladesh Bangladesh
Maksud Saifullah Pulak is a Software Engineer. He have been working more then 2 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

 
GeneralThanks PinprofessionalPaulo Augusto Künzel29-Sep-14 3:30 
QuestionError not sure why Pinmemberlgmanuel12-Aug-14 10:46 
AnswerRe: Error not sure why PinmemberNickysqlboy26-Aug-14 3:55 
QuestionMessage Removed PinmemberMember 1071273913-Jun-14 0:08 
QuestionPivot Table... PinmemberHarnis Findoliya30-May-14 1:41 
GeneralMy vote of 5 Pinmemberlnucleus12-May-14 1:44 
GeneralRe: My vote of 5 PinmemberMaksud Saifullah Pulak18-May-14 1:50 
GeneralMy vote of 5 PinmemberAnimesh Datta4-May-14 22:51 
GeneralRe: My vote of 5 PinmemberMaksud Saifullah Pulak12-May-14 1:02 
GeneralMy vote of 2 PinmemberPawan Mishra14-Apr-14 2:58 
GeneralMy vote of 1 PinmemberKarthik Chintala26-Mar-14 23:08 
BugAvoid keyword PIVOT PinmemberKip Bryan5-Feb-14 7:04 
GeneralRe: Avoid keyword PIVOT PinmemberMaksud Saifullah Pulak3-Mar-14 3:48 
Questionexplain me Pinmemberimtutul11-Jan-14 6:29 
QuestionDynamic columns PinmemberGreesemonkey310-Dec-13 7:37 
GeneralMy vote of 4 Pinmemberdexter.kumar7-Oct-13 0:50 
GeneralRe: My vote of 4 PinprofessionalMaksud Saifullah Pulak29-Nov-13 23:23 
GeneralMy vote of 3 PinmemberMember 101968783-Oct-13 19:01 
GeneralRe: My vote of 3 PinprofessionalMaksud Saifullah Pulak5-Oct-13 7:49 
GeneralMy vote of 1 Pinmemberpavan bhavsar22-Sep-13 7:09 
GeneralMy vote of 5 Pinmemberrishadek12-Sep-13 1:54 
GeneralRe: My vote of 5 PinprofessionalMaksud Saifullah Pulak19-Sep-13 22:38 
GeneralMy vote of 5 Pinmembershreeniwas kushwah22-Aug-13 19:45 
GeneralRe: My vote of 5 PinprofessionalMaksud Saifullah Pulak25-Aug-13 22:27 
QuestionMy doubt Pinmemberdaranee7311-Aug-13 23:32 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 29 Nov 2012
Article Copyright 2012 by Maksud Saifullah Pulak
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid