Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-08
4
Medium Priority
?
3,325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

661 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