Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: Rolling-Up Records

    Well, I can't explain this well on how to do this so I'll put it up like this

    So for example I have this table...

    ID BOOK CLIENTNAME PRICE
    -------------------------------
    1 A client a 100
    2 B client a 200
    3 C client a 100
    4 A client b 100
    5 B client b 50


    And I have to do a query that would put it up like this:

    ID CLIENTNAME BOOK_A BOOK_B BOOK_C
    -------------------------------------------
    1 client a 100 200 100
    2 client b 100 50


    I hope you got what I meant. I'm using MSSQL SERVER 2000


    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT clientname
         , CASE WHEN book = 'A' THEN price END As [book_a]
         , CASE WHEN book = 'B' THEN price END As [book_b]
         , CASE WHEN book = 'C' THEN price END As [book_c]
    FROM   your_table
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    select clientname, max([a]) as [book_a],max([b]) as [book_b],max([c]) as [book_c]
    from @tab
    pivot(max(price) for book in ([a],[b],[c]))pvt
    group by clientname

    if u r using sql2005 it will work
    r try this
    SELECT clientname
    , max(CASE WHEN book = 'A' THEN price END) as [book_a]
    , max(CASE WHEN book = 'B' THEN price END) as [book_b]
    , max(CASE WHEN book = 'C' THEN price END) as [book_c]
    FROM @tab
    group by clientname
    Last edited by bklr; 01-16-09 at 05:38.

Posting Permissions

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