Click here to Skip to main content
13,189,324 members (68,931 online)
Click here to Skip to main content
Add your own
alternative version

Stats

11K views
15 bookmarked
Posted 7 Feb 2015

Pivot Operator in SQL Server Simplified

, 7 Feb 2015
Rate this:
Please Sign up or sign in to vote.
This tip is a brief introduction to Pivot operator in SQL Server

Introduction

In this tip, I am going to explain Pivot Operator in a very simplified way.

According to definition, Pivot is a SQL server operator that can transform unique values from one column in a result-set into multiple columns in the output , hence it seems like rotating the table.

So let's explain what I mean by rotating the table.

Background

I read about it from https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx. I just wanted to explain it in a simple way with an example.

Using the Code

-- Syntax for Pivot operator
SELECT < non - pivoted COLUMN >
    ,[first pivoted column] AS < COLUMN NAME >
    ,[second pivoted column] AS < COLUMN NAME >
    ,...[last pivoted column] AS < COLUMN NAME >
FROM (
    < SELECT query that produces the data >
    ) AS < alias
FOR the source query >
PIVOT(< aggregation FUNCTION > (< COLUMN being aggregated >) _
FOR [<column that contains the values that will become column headers>] IN (
            [first pivoted column]
            ,[second pivoted column]
            ,...[last pivoted column]
            )) AS < alias
FOR the
pivot TABLE > < optional
ORDER BY clause >;

-- This is the code snippet

Create Table Customer
(
 CustomerName nvarchar(50),
 CustomerCountry nvarchar(50),
 SalesAmount int
)

Insert into Customer values('Tommy', 'UK', 200)
Insert into Customer values('Johny', 'US', 180)
Insert into Customer values('Johny', 'UK', 260)
Insert into Customer values('Dave', 'India', 450)
Insert into Customer values('Tommy', 'India', 350)
Insert into Customer values('Dave', 'US', 200)
Insert into Customer values('Tommy', 'US', 130)
Insert into Customer values('Johny', 'India', 540)
Insert into Customer values('Johny', 'UK', 120)
Insert into Customer values('Dave', 'UK', 220)
Insert into Customer values('Johny', 'UK', 420)
Insert into Customer values('Dave', 'US', 320)
Insert into Customer values('Tommy', 'US', 340)
Insert into Customer values('Tommy', 'UK', 660)
Insert into Customer values('Johny', 'India', 430)
Insert into Customer values('Dave', 'India', 230)
Insert into Customer values('Dave', 'India', 280)
Insert into Customer values('Tommy', 'UK', 480)
Insert into Customer values('Johny', 'US', 360)
Insert into Customer values('Dave', 'UK', 140)

-- lets see the result set in our table
Select * from Customer

Now you can see in the result-set above that there are three distinct values of customer name (Tommy, Johny, Dave) and three countries (US, UK, India).

---Now a simple Group By statement can produce a result-set like this :
Select CustomerCountry, CustomerName, SUM(SalesAmount) as Total
from Customer
GROUP BY CustomerCountry,CustomerName
ORDER BY CustomerCountry,CustomerName

You can clearly see CustomerCountry has three values (India, UK and US).

Here comes the power of PIVOT operator that can actually rotate the table. Let's see the output first and I will explain it with its syntax after that.

Pivot operator has performed SUM aggregate function on SalesAmount column for every distinct CustomerCountry column value,
You can see in output data-set - India, UK , US distinct values are pivoted for column CustomerCountry.

Let's see its simple syntax:

Select * from Customer
PIVOT
(
 SUM(SalesAmount) FOR CustomerCountry IN ([India],[UK],[US])
) AS Pivotable

------------------------------------------------------------------------------------------------------------------
Now you have idea of PIVOT, you can also see that in Customer table, there are three distinct customer names (Dave, Johny and Tommy).
So, you can have [Dave], [Johny] and [Tommy] as column headers by Pivoting Sum aggregate function for CustomerName column.

Select * from Customer
PIVOT
(
 SUM(SalesAmount) FOR CustomerName IN ([Dave],[Johny],[Tommy])
) 
AS Pivotable

See in output result:

Now that's it. You have got the basic idea of PIVOT operator.
The other thing that I found time consuming is to type every distinct column values in [ ] brackets like:

[Dave],[Johny],[Tommy]

So, you can have QuoteName, Stuff function in SQL server and for XML that simply does it for you.

DECLARE @quotedcountrynames NVARCHAR(MAX)

SET @quotedcountrynames = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(CustomerCountry)
            FROM Customer
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @quotedcountrynames

You will have output like:

[Dave],[Johny],[Tommy]

or:

[India],[UK],[US] 

which you can feed into IN operator to have those values pivoted for their column names respectively.

Thanks! That's it - I hope you will find it useful.

Points of Interest

I found Stuff function. For XML, it is very useful for taking out the distinct column headers. You can contact me here regarding any query.

License

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

Share

About the Author

Gaurav1641
Web Developer
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionWell thanks to you so much for such nice and healthy ideas. Pin
jacobwilliam7-Feb-15 20:05
memberjacobwilliam7-Feb-15 20:05 
AnswerRe: Well thanks to you so much for such nice and healthy ideas. Pin
livebytes14-Feb-15 10:48
professionallivebytes14-Feb-15 10:48 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171016.2 | Last Updated 7 Feb 2015
Article Copyright 2015 by Gaurav1641
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid