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

Ooops, it got all messed up. Let me know if this doesn't make sense....

Multiple rows returns not by CASE (it's not a possible), but by search condition on WHERE clause.

Lenny

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:
You may be able to get some hint by searching the forum with keyword "pivot/unpivot".

Thanks for your input!