Click here to Skip to main content
13,665,737 members
Click here to Skip to main content
Add your own
alternative version


17 bookmarked
Posted 7 Feb 2015
Licenced CPOL

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


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.


I read about it from 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 >
    < 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
 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
 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:


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('')
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @quotedcountrynames

You will have output like:




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.


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


About the Author

Web Developer
India India
No Biography provided

You may also be interested in...


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 | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.180810.1 | Last Updated 7 Feb 2015
Article Copyright 2015 by Gaurav_Chaudhary
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid