Geeks With Blogs

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

Lorin Thwaits A geek says what?

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!

Posted on Friday, August 4, 2006 6:13 PM SQL | Back to top


Comments on this post: Understanding SQL 2005's new PIVOT clause

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Great info.

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

Thanks.
Left by Jeff Stowe on Aug 11, 2006 9:58 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Great Buddy
Left by Rajib on Sep 17, 2007 3:08 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Thats a great walkthrough.So now how do we get rid of those hard coded employee names?
Left by Haggardpete on Sep 26, 2007 11:46 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Cheers for this great one, bringing light to the darkness...
ZA
Left by ZedAye on Oct 01, 2007 10:31 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
How running a query affect with PIVOT as far as performance is concern
Left by Jag on Oct 17, 2007 9:45 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
thanks. best explanation i've read yet.
Left by dave t on Nov 30, 2007 7:48 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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:)
Left by Micael R on Apr 07, 2008 6:46 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Thanks. This article helped me a lot
Left by venm on Apr 28, 2008 3:13 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
This is very helpful indeed. It 's the clearest explanation about pivot tables I have come across. Many thanks!
Left by Kwan on May 27, 2008 5:46 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
great article. thanks much
Left by Brian on Jun 09, 2008 10:09 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by JT on Sep 25, 2008 3:51 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Is Pivot support in SQLExpress 2005?
Left by AY on Dec 13, 2008 5:43 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Thanks, It was helpfull
Left by Yuv on Dec 14, 2008 10:21 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Greate article, help me to start.
However, you need to hardcode the pivot headers.
Is it possible to do it dynamicly?
Left by Effie on Jan 07, 2009 12:58 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
what is PIVPT ?
Left by pradip on Apr 07, 2009 6:37 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Thank you so much! I'm a very slow learner, but I got it here finally!
Left by Penny on Jul 01, 2009 10:04 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by John Anderson on Jul 07, 2009 9:16 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Great article, it really helps me to understand easily. Thank you
Left by Liliana on Jul 07, 2009 10:48 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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

Left by Sayeed on Jul 17, 2009 7:23 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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!
Left by Mike Grey on Aug 12, 2009 4:04 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Great explanation! Thanks! :)
Left by Vladimir on Aug 26, 2009 11:34 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Your blog is full of funny stuff. Most of all threads are great. I like your blog very much.
Left by funny stuff on Sep 10, 2009 6:50 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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
Left by saikrishna on Oct 04, 2009 4:58 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Can I get help with PIVOT Clause when I need to use MAX instead of count or Sum
Left by Pitso on Oct 09, 2009 3:25 AM

# Rows into Columns
Requesting Gravatar...
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.

Left by Hari on Nov 05, 2009 4:02 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
wo ta ma de fande yibi a
Left by DDos Protection on Nov 06, 2009 11:15 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by Samir on Dec 10, 2009 11:27 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by SEO Reseller on Dec 11, 2009 5:48 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
How can I sound proof a small telecommunications equipment cabinet in an office?
Left by monitor stands on Dec 29, 2009 1:29 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
What is the best web host for a dreamweaver made html web site?
Left by monitor stands on Jan 07, 2010 2:01 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
What Should be the Name of a Website dealing with Online Marketing Business and B2B services ?
Left by Business listings on Jan 07, 2010 6:35 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Great examples and explanation, I finally learned and badly needed help on this.
Left by Muhammad Ali on Jan 22, 2010 6:17 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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?
Left by cashdesk on Mar 05, 2010 3:01 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
I really loved this gorgeous website. Please keep them coming. Regards from Jason!!
Left by sim date game on Mar 06, 2010 6:51 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
This is really cool information, thanks a lot !!
Left by Adrian Facio Medina on Mar 10, 2010 4:13 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Thanks for the review, however i think you have to improve a bit the design and usability of your blog.
Left by us drugstore on Mar 14, 2010 6:46 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by apotek online on Mar 22, 2010 10:23 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
There are some major techniques in SQL 2005.... which makes us solve the query in very few time..... and I really the article.....

Thanks
Left by on Mar 29, 2010 2:02 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
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.
Left by tinggi badan on May 21, 2010 12:56 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
This is so cool!!! I bookmarked this blog a while ago because of the great content and I am never being dissapointed.
Left by apteka online on Jun 05, 2010 12:26 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
I really loved this gorgeous website. Please keep them coming. Regards Jhonny
Left by on Jul 24, 2010 5:38 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
How to prepare SQL skills for the real world?
Left by SEO Companies on Aug 11, 2010 5:16 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Nice, I've been looking for posts like this. Thanks for sharing! Nice Post!
Left by on Aug 11, 2010 2:07 PM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
Can you tell me the procedure to install the SQL software?
Left by New York City SEO on Aug 17, 2010 2:03 AM

# re: Understanding SQL 2005's new PIVOT clause
Requesting Gravatar...
yes always been reading and writing texts like this in blogs. Also, I, as a daily writer, present my respects to everyone.
Left by wholesale clothing on Aug 20, 2010 10:39 PM

Your comment:
 (will show your gravatar)


Copyright © Lorin Thwaits | Powered by: GeeksWithBlogs.net | Join free