Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Create new fields from distinct data in fields

    I have a table that looks like this:
    Code:
    AcctID    Type    Amount
    1234        A          1
    1234        A          1
    1234        B          1
    1234        C          1
    2345        A          1
    2345        B          1
    2345        B          1
    I want to query it such that the results would be this:

    Code:
    AcctID     A    B    C
    1234        2    1    1
    2345        1    2    0
    I know I can do it by specifically summing up A, B, and C as separate "case when" statements; however, the type is not always going to be A, B, or C and could be any variation of 10,000+ amounts so I need it to work dynamically.

    This is SQL Server 2005. I'm an end user with read-only permissions, though I can create temp tables.

    Thanks

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    SELECT *
    FROM [table]
    PIVOT
    (
    SUM(Amount)
    FOR [Type] IN ([A],[B],[C])
    )
    AS p

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gagnon's reply though correct suffers from the same problem you identify with CASE.

    In short - SQL can't do this easily and arguably should not do this at all. If you are consuming the data with a tool like Excel, Business Objects, Reporting Services etc. then pivot the data there.

    Otherwise you should google some dynamic code to build up your SQL. With 10k + columns though you might not even be able to do that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Over at sqlteam.com they have a dynamic pivot procedure, which I have never checked out because (as Pootle points out above) THIS SHOULD NEVER BE DONE IN A DATABASE ANYWAY!

    Export a flatfile recordset and let your application dictate how it is summarized and presented.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Oct 2010
    Posts
    2
    I was able to make it work by combining Gagnon's suggestion and dynamically building the IN ([A],[B],[C]) part.

    It's not particularly elegant, though. Guess I'll stick with my current strategy of using Excel and begging for Business Objects.

    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Business Objects?
    Why not try SQL Server Reporting Services?
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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