SQL Server Performance

PIVOT operator

Discussion in 'SQL Server 2005 General Developer Questions' started by Madhivanan, Jun 20, 2007.

  1. Madhivanan Moderator

    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
  2. masri999 New Member

    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
  3. Madhivanan Moderator

    So, only way is to define all EmployeeIds?


    Madhivanan

    Failing to plan is Planning to fail
  4. Madhivanan Moderator

    So, can I assume that this is not possible?

    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    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.
  6. Madhivanan Moderator

    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
  7. Adriaan New Member

    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 ...
  8. Madhivanan Moderator

    Well. I started learning SQL Server 2005 [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  9. satya Moderator

    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.
  10. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Good, then you can start answering 2005 sections soon.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. Also you can expect more questions from me [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. satya Moderator

    [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] that we can see already...[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>
  12. Madhivanan Moderator

    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
  13. Adriaan New Member

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

    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
  15. satya Moderator

    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.
  16. Madhivanan Moderator

    Thanks. I will read it and understand what goes on

    Madhivanan

    Failing to plan is Planning to fail
  17. Adriaan New Member

    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 ...
  18. Madhivanan Moderator

    That seems cool. I wonder why there is no such way in 2005

    Madhivanan

    Failing to plan is Planning to fail

Share This Page