asked on

Oracle SQL statement needed to align column values across the record

I have a resultset like the following:

Name Type Hours
--------------- ---------- -------------
John Direct 10
Jane Indirect 20
Mary Direct 15

That I need to look like this:

Name Direct Indirect
------------- ------------- ------------
John 10 0
Jane 0 20
Mary 15 0

I think this is possible can somebody provide the Oracle SQL?

Thanks.

ASKER CERTIFIED SOLUTION

mukhtar2t

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Zilvinas Zukauskas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mukhtar2t

Yes that it is i forgot the 0
select
name,
decode(Type,'Direct',Hours,'Indirect',0) Direct,
decode(Type,'Indirect',Hours,'Direct',0) Indirect
from
thetable

harry_hendrata

try this, (x is thetable)

select tab1.name, NVL(tab2.hours,0) DIRECT,
NVL(tab3.hours,0) INDIRECT
from x tab1,
x tab2,
x tab3
where tab1.name = tab2.name (+)
and tab1.name = tab3.name (+)
and tab2.type (+) = 'DIRECT'
and tab3.type (+) = 'INDIRECT'

SOLUTION

YANN0S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YANN0S

Sorry I forgot: I would not prefer the self outer join solution, Why adding overhead with joins etc?

mukhtar2t

Yes while there is a solution without join that is better