Pivot Operator in SQL Server Simplified

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.