Solved

Can a Query flatten multiple rows into single named columns? - Oracle 10g

Posted on 2006-11-08
4
3,201 Views
Last Modified: 2008-01-09
I could design a function with a cursor to return the requested data below but would like to know if you can recommend a better design method such as an efficient Oracle query or use of Analytics??  

We have a large Oracle 10g table, Tbl_Events, that keeps track of the scheduled date, completed date, and cost for each process (event) that is scheduled or completed on our “Files”.

Not every one of our “Files” requires the same processes (events).

An example of data in this table, where two “Files” are being processed (1001 and 1002) and the associated events' completed and scheudled dates and costs for each of these “Files”:  

File      Event      Sched_Date      Compl_Date         Cost_Amt            
1001      A1      10/01/2006      10/02/2006       20.10
1001      A2      10/03/2006                  40.00
1001      B1      10/01/2006                        
1001      B2                                    
1002      A1                  09/01/2006      50.00
1002      B2      09/15/2006      09/14/2006      35.50
1002      B3      10/02/2006      

The "query" will need return values for specific events for all "Files" in a single row per “File”.

For example, if I only want the A1 and B3 Events for the “Files” in the table above I would want to design a query to return the following rows (one for each “File”) and columns:

File      A1_Sched            A1_Compl            A1_Amt      B3_Sched      B3_Compl       B3_amt
1001      10/01/2006      10/02/2006      20.10      NULL      NULL      NULL
1002      09/15/2006      NULL             50.00      10/2/2006      NULL      NULL

I assume that I will need to hard code the desired Events and resulting column names.

Thanks in advance,

TXAmmonite (David)
0
Comment
Question by:TXAmmonite
4 Comments
 
LVL 18

Accepted Solution

by:
rbrooker earned 150 total points
ID: 17902219
Hi,

what about :

select file,
  max( decode( event, 'A1', sched_date, null ) ) a1_sched,
  max( decode( event, 'A1', compl_date, null ) ) a1_compl,
  max( decode( event, 'A1', cost_amt, null ) ) a1_amt,
  max( decode( event, 'B3', sched_date, null ) ) b3_sched,
  max( decode( event, 'B3', compl_date, null ) ) b3_compl,
  max( decode( event, 'B3', cost_amt, null ) ) b3_amt
from my_table
group by file

good luck :)
0
 
LVL 7

Expert Comment

by:Stephen Lappin
ID: 17905388
Sounds like you are looking for a crosstab-type function.

Have a look at this article - it should deliver what you want.
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7086279412131
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 350 total points
ID: 17906070
Using analytics: (Btw it was fun writing it with analytics)

select file1, a1_sched, a1_compl, a1_amt, b3_sched, b3_compl, b3_amt
from
(
select file1, sched_date a1_sched, compl_date a1_compl, cost_amt a1_amt,
row_number() over(partition by file1 order by event ) rn,
lead(sched_date,1) over(partition by file1 order by event ) b3_sched,
lead(compl_date,1) over(partition by file1 order by event ) b3_compl,
lead(cost_amt,1) over(partition by file1 order by event ) b3_amt
from c
where event in ('A1', 'B3')
)
where rn = 1
/
0
 

Author Comment

by:TXAmmonite
ID: 17931927
This is the first time I have split points - not sure I did this correctly...

Thanks everyone for responding.  Both rbooker's answer and sujith80's examples worked.

But sujith80's Analystics example was far more efficient (time and cost plan) that rbooker's.  I did have to add an "Order By" Clause on the analytics query to ensure that the event values print in the correct columns in every case:


select file1, a1_sched, a1_compl, a1_amt, b3_sched, b3_compl, b3_amt
from
(
select file1, sched_date a1_sched, compl_date a1_compl, cost_amt a1_amt,
row_number() over(partition by file1 order by event ) rn,
lead(sched_date,1) over(partition by file1 order by event ) b3_sched,
lead(compl_date,1) over(partition by file1 order by event ) b3_compl,
lead(cost_amt,1) over(partition by file1 order by event ) b3_amt
from c
where event in ('A1', 'B3')  --added line
ORDER BY file1, event       -- added line
)
where rn = 1
/
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question