Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1389
  • Last Modified:

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.
0
dkbailey1
Asked:
dkbailey1
3 Solutions
 
mukhtar2tCommented:
select name,decode(type,'direct',hours,) Direct,,decode(type,'indirect',hours,) InDirect,
0
 
zilvaCommented:
select
  name,
  decode(Type,'Direct',Hours,'Indirect',0) Direct,
  decode(Type,'Indirect',Hours,'Direct',0) Indirect
from
  your_table
0
 
mukhtar2tCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
harry_hendrataCommented:
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'
0
 
YANN0SCommented:
All the solutions are correct if Name is unique in that table. A more generic approach is the following:

SELECT NAME,
       SUM(DECODE (TYPE, 'Direct', hours, 0)) direct,
       SUM(DECODE (TYPE, 'Indirect', hours, 0)) indirect
  FROM your_table
 GROUP BY NAME

Pls note that I have changed the DECODE expression to DECODE (TYPE, 'Direct', hours, 0) instead of decode(Type,'Direct',Hours,'Indirect',0) (i.e. defaulting to 0 when type is different than 'Direct'). This can be usefull if you have more than one distinct values in TYPE column.

SELECT NAME,
       SUM(DECODE (TYPE, 'Direct', hours, 0)) direct,
       SUM(DECODE (TYPE, 'Indirect', hours, 0)) indirect,
       SUM(DECODE (TYPE, 'Other', hours, 0)) other
  FROM your_table
 GROUP BY NAME

Unfortunatelly you have to know the distinct values of the TYPE column before writing the statement (since you have to know, therefore there is no single statement that would work for 2,3,4,...etc distinct values of TYPE).
0
 
YANN0SCommented:
Sorry I forgot: I would not prefer the self outer join solution, Why adding overhead with joins etc?
0
 
mukhtar2tCommented:
Yes while there is a solution without join that is better
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now