Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Unanswered: Advance PIVOT function in SQL2005

    Below is an example of a pivot table from the help in SQL2005.
    My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?

    I would like to use this for a daily report where the columns would be the dates?

    Thanks.

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is not standardized SQL. You may want to ask in a product-specific group
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by db8s7795
    My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
    Yes. To have an unknown number of columns\ values you need to use dynamic sql.
    http://www.sommarskog.se/dynamic_sql.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •