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

x
Solved

# Oracle 11 - side by side data different date criteria

Posted on 2011-03-07
Medium Priority
278 Views
Hello

My original query ..
select
col1,
col2,
date_year
from table1
where date_year = '2009'

Result set

col1  col2   date_year
a      12      2009
b      14      2009

What I now want to achieve is another result set to the right but with
previous year data - I do not want to hard code this bit - how can I do this.
I only want to parameterize the year 2009
So, my result set would now be...
col1  col2   date_year  col1a  col1b     date_year
a      12      2009      a      45         2008
b      14      2009      b      16         2008
0
Question by:philsivyer
[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
• 4
• 3

LVL 77

Expert Comment

ID: 35058827
What is the correlation?

What 2009 colb goes with what 2008 colb?

More sample data and expected results would help.
0

Author Comment

ID: 35058959
Hope this makes sense..
Query 1
select
col1,
col2,
date_year
from table1
where date_year = '2009'

Result set....
col1  col2   date_year
a      12      2009
b      14      2009
..............................
Query 2
select
col1,
col2,
date_year
from table1
where date_year = '2008'
Result set....
col1  col2   date_year
a      15.5    2008
b      11      2008

So, I would like one query which could return the following data

col1  col2   date_year col1(1) col2(1)  date_year(1)
a      12      2009     a       15.5      2008
b      14      2009     b       11        2008
0

LVL 77

Expert Comment

ID: 35059001
So for col1 there is always a one-to-one correlation across years?

On other words: for col1='a', there is only EVER a single row per year.
0

Author Comment

ID: 35059017
Correct
0

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 35059159
Try this
``````drop table tab1 purge;
create table tab1 (col1 char(1), col2 number, datE_year char(4));

insert into tab1 values('a',12,'2009');
insert into tab1 values('b',14,'2009');
insert into tab1 values('a',15.5,'2008');
insert into tab1 values('b',11,'2008');
commit;

select col1, col2, date_year, prev_col1, prev_col2, prev_year
from
(
select col1, col2, date_year,
lead(col1) over(partition by col1 order by col1, date_year desc) prev_col1,
lead(col2) over(partition by col1 order by col1, date_year desc) prev_col2,
lead(date_year) over(partition by col1 order by date_year desc) prev_year
from tab1
)
where date_year = '2009'
/
``````
0

Author Comment

ID: 35069048
Thanks - this works.
0

Author Closing Comment

ID: 35069050
Thanks
0

## Featured Post

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month8 days, 21 hours left to enroll