SQL Server 2005 has lots of new tricks up its sleeve.  During our user group meeting this past Wednesday night there was a question about how to use one such enhancement: the PIVOT statement.  You can think of this like a two dimensional GROUP BY.  It's the same sort of idea as Excel's pivot tables, or the Matrix control from Reporting Services.  In this post we'll walk through the process of converting a simple query with JOIN and GROUP BY to use PIVOT.

The sample code acts on the Customers, Orders, and Employees tables in the Northwind database.  If you don't have a copy of Northwind on your instance of SQL 2005, just run the script found in this .ZIP file.

So to start with, here's a quick query that uses a JOIN to find which salespeople sold product to which customer.  One row for each sale will be shown:

SELECT e.firstName,c.CompanyName
 FROM Employees e
 JOIN Orders o ON e.employeeID=o.employeeID
 JOIN Customers c ON c.customerID=o.customerID
 
ORDER BY e.firstName,c.CompanyName
   

Compelling, but we see that there's many repeated entries in the list.  We'd like to count up how many sales are at the intersection of each distinct customer and salesperson.  To do this we'll add in a COUNT function and GROUP BY clause.

SELECT e.firstName,c.CompanyName, COUNT(o.orderID)
 FROM Employees e
 JOIN Orders o ON e.employeeID=o.employeeID
 JOIN Customers c ON c.customerID=o.customerID
 GROUP BY e.firstName,c.CompanyName
 
ORDER BY e.firstName,c.CompanyName
   

Now the real fun begins as we transition this into a query that uses PIVOT.  Because we're grouping by not one but two columns, this query is a good candidate for PIVOT.  We'll start simple with a query that doesn't JOIN anything, but just references the Orders table.  We'll choose to have Employee data put into columns since there are only 9 employees.  Much more manageable.  In this case the numbers 1 through 9 refer to the ID of employees:

SELECT * FROM Orders
 
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

In the result set notice that the specific columns returned come from the result of the PIVOT clause and not directly from the Orders table!  And that PIVOT clause brings up every column from the Orders table except for employeeID, which gets weeded out since it is used in the aggregate function COUNT.  If it helps to visualize what's going on here, it's as if we had said “GROUP BY *” if such a thing existed.  Another interesting thing about PIVOT is that there can be only one aggregate function and column referenced.  In this case it's COUNT(employeeID).

To limit the columns returned to just what we need to show and aggregate, we'll have to use a subquery.  A subquery like this is necessary in almost every PIVOT query you would ever want to run.  In this case we want to see a count of orders based on customerID and employeeID, so the subquery will include those columns.

SELECT * FROM (SELECT customerID,employeeID FROM Orders) o
 
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

The aliases “o” and “p” are essential!
Although we don't use it anywhere else in the query, the alias “o” on the subquery (or really any alias at all) is required.  The reason is that an unnamed table-valued parameter cannot be used as part of the PIVOT clause.  So “o” gets referenced internally by PIVOT, and is never exposed anywhere else.  If we want to specify a column in any way in the rest of the query such as in the column list or when JOINing to another table, we would instead use what PIVOT spits out, which is table-valued and given the alias “p”.  Even if we don't need to reference a column emitted from PIVOT (as seen in the above query), some kind of alias is still required.

So now let's update the column list to show friendlier names for the employeeID columns.  Unfortunately each column you want to show this way needs to be hard-coded:

SELECT

customerID,
 
[1] as [Nancy],
 
[2] as [Andrew],
 
[3] as [Janet],
 
[4] as [Margaret],
 
[5] as [Steven],
 
[6] as [Michael],
 
[7] as [Robert],
 
[8] as [Laura],
 
[9] as [Anne]
 FROM (SELECT customerID,employeeID FROM Orders) o
 
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

Our final trick will be to show friendly names for the customers by JOINing in that table, and referencing the companyName column.  We'll also do an ORDER BY with this column:

SELECT c.companyName,
 
[1] as [Nancy],
 
[2] as [Andrew],
 
[3] as [Janet],
 
[4] as [Margaret],
 
[5] as [Steven],
 
[6] as [Michael],
 
[7] as [Robert],
 
[8] as [Laura],
 
[9] as [Anne]
 FROM (SELECT customerID,employeeID FROM Orders) o
 
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p
  JOIN Customers c ON p.customerID=c.customerID
 ORDER BY c.CompanyName

Again note that in the JOIN we have to use “p.cusomterID” rathar than “o.customerID” since the alias “o” is only made available internally in PIVOT.

Hopefully you enjoyed this tour through the new PIVOT statement in SQL 2005.  But if not, hey, at least you got this info for free!


Feedback

# re: Understanding SQL 2005's new PIVOT clause

Great info.

It helps me understand what is going on internally with the PIVOT clause.

Thanks. 8/11/2006 9:58 AM | Jeff Stowe

# re: Understanding SQL 2005's new PIVOT clause

Great Buddy 9/17/2007 3:08 AM | Rajib

# re: Understanding SQL 2005's new PIVOT clause

Thats a great walkthrough.So now how do we get rid of those hard coded employee names? 9/26/2007 11:46 PM | Haggardpete

# re: Understanding SQL 2005's new PIVOT clause

Cheers for this great one, bringing light to the darkness...
ZA 10/1/2007 10:31 AM | ZedAye

# re: Understanding SQL 2005's new PIVOT clause

How running a query affect with PIVOT as far as performance is concern 10/17/2007 9:45 AM | Jag

# re: Understanding SQL 2005's new PIVOT clause

thanks. best explanation i've read yet. 11/30/2007 7:48 AM | dave t

# re: Understanding SQL 2005's new PIVOT clause

Thx, great article, really helpful!
Put this code in a stored procedure with a user defined function, then we can get rid of those hard coded employee names:)
4/7/2008 6:46 PM | Micael R

# re: Understanding SQL 2005's new PIVOT clause

Thanks. This article helped me a lot 4/28/2008 3:13 AM | venm

# re: Understanding SQL 2005's new PIVOT clause

This is very helpful indeed. It 's the clearest explanation about pivot tables I have come across. Many thanks! 5/27/2008 5:46 AM | Kwan

# re: Understanding SQL 2005's new PIVOT clause

great article. thanks much 6/9/2008 10:09 AM | Brian

# re: Understanding SQL 2005's new PIVOT clause

If you use Max() or Min() as the aggregation function, pivot works on varchar field lists with one value, preserving that value in the output. 9/25/2008 3:51 AM | JT

# re: Understanding SQL 2005's new PIVOT clause

Is Pivot support in SQLExpress 2005? 12/13/2008 5:43 PM | AY

# re: Understanding SQL 2005's new PIVOT clause

Thanks, It was helpfull 12/14/2008 10:21 PM | Yuv

# re: Understanding SQL 2005's new PIVOT clause

Greate article, help me to start.
However, you need to hardcode the pivot headers.
Is it possible to do it dynamicly? 1/7/2009 12:58 AM | Effie

# re: Understanding SQL 2005's new PIVOT clause

what is PIVPT ? 4/7/2009 6:37 PM | pradip

# re: Understanding SQL 2005's new PIVOT clause

Thank you so much! I'm a very slow learner, but I got it here finally!
7/1/2009 10:04 AM | Penny

# re: Understanding SQL 2005's new PIVOT clause

How would I go about adding a Total column to the end? I.e., for the first row in the last example graphic, a Total column for [Alfreds Futterkiste] would be [6], the sum of Nancy's, Janet's, Margaret's, and Michael's numbers.

Thanks for a great post; it really helped me get a PIVOT query working. 7/7/2009 9:16 AM | John Anderson

# re: Understanding SQL 2005's new PIVOT clause

Great article, it really helps me to understand easily. Thank you 7/7/2009 10:48 AM | Liliana

# re: Understanding SQL 2005's new PIVOT clause

Ya it is working fine but one restriction is there that I have to submit the employeeid manually, so it is restricted. See below

PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])

But if suppose I have to select EmployeeId dynamically from table-emp my revised coding will look like this

PIVOT (COUNT(employeeID) FOR employeeID IN (select empid from emp)

But unfortunately it not works.

Please extend information

Regards

Sayeed

7/17/2009 7:23 AM | Sayeed

# re: Understanding SQL 2005's new PIVOT clause

That's right Sayeed.

I don't know who it works with variable Employees, that's the joke!! Adding N columns, where N is the count of different Employees.

I will go on searching the web.

Good look! 8/12/2009 4:04 PM | Mike Grey

# re: Understanding SQL 2005's new PIVOT clause

Great explanation! Thanks! :) 8/26/2009 11:34 AM | Vladimir

# re: Understanding SQL 2005's new PIVOT clause

Your blog is full of funny stuff. Most of all threads are great. I like your blog very much. 9/10/2009 6:50 PM | funny stuff

# re: Understanding SQL 2005's new PIVOT clause

Hi sayeed i had solution for you TRY THIS

DECLARE @States nvarchar(max)
SELECT @States =
STUFF(
(
select distinct ',[' + S.StateProvinceCode + ']'
from Person.Address A
inner join Person.StateProvince S
on s.StateProvinceID = A.StateProvinceID
for xml path('')
),
1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
*
from (
select
A.AddressID,
S.StateProvinceCode
from Person.Address A
inner join Person.StateProvince S
on s.StateProvinceID = A.StateProvinceID
) Data
PIVOT (
Count(AddressID)
FOR StateProvinceCode
IN (
' + @States + '
)
) PivotTable
'

exec sp_executesql @SQL 10/4/2009 4:58 AM | saikrishna

# re: Understanding SQL 2005's new PIVOT clause

Can I get help with PIVOT Clause when I need to use MAX instead of count or Sum 10/9/2009 3:25 AM | Pitso

# Rows into Columns

I hav 10 fruits name in my dropdownlist like(Apple, Orange,Banana, Grapes,Lemon, Strawberry, etc.,). But, I want to retrieve only the inserted fruit name as column name.I do not know what are the Fruit_Names wil come in [Fruits] field.
But I want to convert into column name. Is it possible to get Column name dynamically in SQL 2005.

Eg,

Fruits Kg Person_ID
_______________________________

Apple 2 201
Orange 3 201
Banana 2 201
Grapes 1 201
Apple 2 523
Orange 1 523
Banana 5 523
Grapes 2 523

I want my table as below

Person_ID Apple Orange Banana Grapes
______________________________________________
201 2 3 2 1
523 2 1 5 2


Pls, Explain anyone.
Tks in Advnce.

11/5/2009 4:02 AM | Hari

# re: Understanding SQL 2005's new PIVOT clause

wo ta ma de fande yibi a 11/6/2009 11:15 PM | DDos Protection

# re: Understanding SQL 2005's new PIVOT clause

I was trying to solve a performance issue using PIVOT and couldn't really find out how to use it correctly. I found your site and actually it made my day. My query is working fine and I am getting the exact result I need. I will replace tens of queries to build a report by only one query using PIVOT... I belive that my report will fly now.
Thanks so much for this post. Clear and straight to the point. 12/10/2009 11:27 AM | Samir

# re: Understanding SQL 2005's new PIVOT clause

When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90. 12/11/2009 5:48 AM | SEO Reseller

# re: Understanding SQL 2005's new PIVOT clause

How can I sound proof a small telecommunications equipment cabinet in an office? 12/29/2009 1:29 AM | monitor stands

# re: Understanding SQL 2005's new PIVOT clause

What is the best web host for a dreamweaver made html web site?
1/7/2010 2:01 AM | monitor stands

# re: Understanding SQL 2005's new PIVOT clause

What Should be the Name of a Website dealing with Online Marketing Business and B2B services ?
1/7/2010 6:35 AM | Business listings

# re: Understanding SQL 2005's new PIVOT clause

Great examples and explanation, I finally learned and badly needed help on this. 1/22/2010 6:17 AM | Muhammad Ali

# re: Understanding SQL 2005's new PIVOT clause

I have SQL server 2005 and tried to run this example from above:

SELECT * FROM Orders
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9]))

The result was:

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.

Any ideas? 3/5/2010 3:01 AM | cashdesk

# re: Understanding SQL 2005's new PIVOT clause

I really loved this gorgeous website. Please keep them coming. Regards from Jason!! 3/6/2010 6:51 PM | sim date game

# re: Understanding SQL 2005's new PIVOT clause

This is really cool information, thanks a lot !! 3/10/2010 4:13 PM | Adrian Facio Medina

# re: Understanding SQL 2005's new PIVOT clause

Thanks for the review, however i think you have to improve a bit the design and usability of your blog. 3/14/2010 6:46 AM | us drugstore

# re: Understanding SQL 2005's new PIVOT clause

Nice information, valuable and excellent, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which we all need, thanks for all the enthusiasm to offer such helpful information here. 3/22/2010 10:23 AM | apotek online

# re: Understanding SQL 2005's new PIVOT clause

There are some major techniques in SQL 2005.... which makes us solve the query in very few time..... and I really the article.....

Thanks 3/29/2010 2:02 AM |

# re: Understanding SQL 2005's new PIVOT clause

You can think of this like a two dimensional GROUP BY. It's the same sort of idea as Excel's pivot tables, or the Matrix control from Reporting Services. In this post we'll walk through the process of converting a simple query with JOIN and GROUP BY to use PIVOT. 5/21/2010 12:56 AM | tinggi badan

# re: Understanding SQL 2005's new PIVOT clause

This is so cool!!! I bookmarked this blog a while ago because of the great content and I am never being dissapointed. 6/5/2010 12:26 AM | apteka online

# re: Understanding SQL 2005's new PIVOT clause

I really loved this gorgeous website. Please keep them coming. Regards Jhonny 7/24/2010 5:38 PM |

# re: Understanding SQL 2005's new PIVOT clause

How to prepare SQL skills for the real world? 8/11/2010 5:16 AM | SEO Companies

# re: Understanding SQL 2005's new PIVOT clause

Nice, I've been looking for posts like this. Thanks for sharing! Nice Post! 8/11/2010 2:07 PM |

# re: Understanding SQL 2005's new PIVOT clause

Can you tell me the procedure to install the SQL software? 8/17/2010 2:03 AM | New York City SEO

# re: Understanding SQL 2005's new PIVOT clause

yes always been reading and writing texts like this in blogs. Also, I, as a daily writer, present my respects to everyone. 8/20/2010 10:39 PM | wholesale clothing

Post a comment





 

 

News


Welcome to my blog.
Here's what we've got on the menu today:

Tag Cloud


Article Categories

Archives

Post Categories

Image Galleries

Syndication: