Solved

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

Posted on 2006-11-08
4
3,162 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:lappins
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now