Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Cross Tab Report

    Using SQL Server 2005. Is there a way to create a cross tab report and then save it into a table only using SQL Code. Thank you for your help. David

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is
    Code:
    select somekey
         , sum(case when foo = 'boo' then 1 else 0 end) as boo
         , sum(case when bar = 'car' then 1 else 0 end) as car
         , sum(case when qux = 'tux' then 1 else 0 end) as tux
      into newtable
      from oldtable
    group
        by somekey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    OK.. Let me see what I can do. Thank you David

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    2005 has the new PIVOT function:
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's sweet

    by the way, where does the INTO clause go?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Between Select and From, I suppose.

Posting Permissions

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