Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-08
4
Medium Priority
?
3,352 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 600 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 1400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

877 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