SQL Server Performance Forum – Threads Archive

PIVOT operator

This is the sample code in sql server help file USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
Which will give count for the specified EmployeeIds only
Now how do I change it so that it will give count for all Employees? Madhivanan Failing to plan is Planning to fail

Number of employess may be high, number of columns will exceed the limit .
You can take all the employee ids and hardcode otherwise need to use dynamic pivot
M A Srinivas

So, only way is to define all EmployeeIds?
Madhivanan Failing to plan is Planning to fail

So, can I assume that this is not possible? Madhivanan Failing to plan is Planning to fail

I don’t know SQL 2005 syntax, but can’t you do this … FOR EmployeeID IN (SELECT EmployeeID FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID) Perhaps a TOP clause might be a good idea there.

Thanks Adriaan. I was thinking to do that. But I wonder if there is any other way other than subquery like the following COUNT (PurchaseOrderID) FOR ALL EmployeeID Madhivanan Failing to plan is Planning to fail

Sorry, I’m a SQL 2000 die-hard, with no access to SQL 2005 at all, so I can only make suggestions off the top of my head …

Well. I started learning SQL Server 2005 [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Good, then you can start answering 2005 sections soon. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Good, then you can start answering 2005 sections soon. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> Yes. Also you can expect more questions from me [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] that we can see already…[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

quote:Originally posted by Adriaan I don’t know SQL 2005 syntax, but can’t you do this … FOR EmployeeID IN (SELECT EmployeeID FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID) Perhaps a TOP clause might be a good idea there.

Seems that wont work
It throws the error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘select’.
Server: Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘)’.
Madhivanan Failing to plan is Planning to fail

IMHO, if the PIVOT syntax forces you to spell out the header values, then it isn’t much good.

quote:Originally posted by Adriaan IMHO, if the PIVOT syntax forces you to spell out the header values, then it isn’t much good.

So, you meant that the syntax is not generalised? Madhivanan Failing to plan is Planning to fail

I think it is by default for PIVOT operator to note that the list of IN values MUST be a pre-determined, hard-coded value. The value you PIVOT for (in this case, PurchaseOrderID) must equal one of the enclosed values. If the list of enclosed values is variable, you must construct the query string manually and use dynamic SQL. This is where Common Table Expression are usefulhttp://www.4guysfromrolla.com/webtech/071906-1.shtml (I think). Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks. I will read it and understand what goes on Madhivanan Failing to plan is Planning to fail

There are other languages that offer a much more flexible syntax, not requiring dynamic SQL. For example in Jet SQL the query would look something like this: TRANSFORM COUNT(t.PurchaseOrderID) AS CountOfPurchaseOrderID
SELECT t.VendorID
FROM Purchasing.PurchaseOrderHeader t
GROUP BY t.VendorID
PIVOT t.EmployeeID; … where you can add (derived) tables to the FROM clause, and use PIVOT and TRANSFORM whichever way you need, even pivoting by expressions …

That seems cool. I wonder why there is no such way in 2005 Madhivanan Failing to plan is Planning to fail

]]>