Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: Multiple rows as a result of case statement

    Hello,

    Here's a scenario

    table

    Acct Prod Status
    acct1 1 A
    acct1 2 C
    acct2 2 A
    acct2 1 C


    select distinct Acct, case when Prod = 1 then Status end as Prod 1, case when Prod = 2 then Status end as Prod 2
    from table.......

    I expect to have one row per Acct with 2 columns, one for each products

    Acct Prod 1 Prod 2
    acct1 A C
    acct2 C A

    but getting this

    Acct Prod 1 Prod 2
    acct1 A
    acct1 C
    acct2 C
    acct2 A

    this is driving me up the wall, I know this worked at my previous company with Oracle, but it doesn't here with DB2. Can somebody please help?!

    Thanks,
    VA

  2. #2
    Join Date
    Dec 2009
    Posts
    3
    Ooops, it got all messed up. Let me know if this doesn't make sense....

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Multiple rows returns not by CASE (it's not a possible), but by search condition on WHERE clause.

    Lenny

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To get:
    Acct Prod 1 Prod 2
    acct1 A C
    acct2 C A

    From:
    table
    Acct Prod Status
    acct1 1 A
    acct1 2 C
    acct2 2 A
    acct2 1 C


    You can try this query:
    Code:
    SELECT Acct
         , MAX(CASE Prod WHEN 1 THEN Status END) AS Prod_1
         , MAX(CASE Prod WHEN 2 THEN Status END) AS Prod_2
      FROM table
     GROUP BY
           Acct
    ;
    You may be able to get some hint by searching the forum with keyword "pivot/unpivot".

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    Thanks for your input!

Posting Permissions

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