Join on the same table in DB2

Question

I have a table like

col1 col2 col3
abc  123  A
abc  345  B
def  567  A
ghi  890  B

I want to have the data in one row related to an element. Col3 is the discriminator element. Only the 'A' and 'B' rows are related. Result should look like

col1 col2A col2B
abc  123   345
def  567   --
ghi  --    890

How can i do that??

Answer 1

This construct works in SQL Server. I do not have a DB2 instance handy to verify that it works there as well, but the syntax is reasonably standard.

SELECT t0.col1, t1.col2 AS col2A, t2.col2 AS col2B
FROM (SELECT DISTINCT col1 FROM mytable) as t0 -- Select the list of keys
LEFT OUTER JOIN mytable t1 ON t0.col1=t1.col1 AND t1.col3='A' -- Join for col2A
LEFT OUTER JOIN mytable t2 ON t0.col1=t2.col1 AND t2.col3='B' -- Join for col2B

Answer 2

That might not be case to use self join. This will work for the data you posted:

select 
    col1,
    max(case col3 when 'A' then col2 else null end) col2A,
    max(case col3 when 'B' then col2 else null end) col2B
from YourTable
group by col1

Of course, if you have data like below, it would fail:

col1 col2 col3
abc  123  A
abc  345  B
abc  567  A

Then again, not only the query would fail, but your whole business rule would fail if you have data like this.