Solved

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

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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