Home » SQL & PL/SQL » SQL & PL/SQL » Flattening out a table (Oracle 10G)
| Flattening out a table [message #587283] |
Thu, 13 June 2013 15:35  |
 |
mxz25
Messages: 2
Registered: June 2013
|
Junior Member |
|
|
I have a product_price table it looks like this:
Prod_ID VARCHAR2(32)
Prod_Prc_Cd VARCHAR2(10)
Prod_Prc NUMBER(10,2)
Prod_Eff_dt DATE
prod_id Prod_Prc_Cd Prod_Prc Prod_Eff_dt
3256 11 23.46 11/20/2011
3256 12 25.06 12/22/2012
3256 15 34.61 3/11/2013
4353 11 45.26 10/10/2012
4353 15 21.12 1/06/2013
5612 12 44.11 02/15/2013
I want to flatten it out like:
prod_id APP APP_EFF_DT WHP WHP_EFF_DT DRP DRP_EFF_DT
3256 23.46 11/20/2011 25.06 12/22/2012 34.61 3/11/2013
4353 45.26 10/10/2012 21.12 1/06/2013
5612 44.11 02/15/2013
|
|
|
|
|
|
|
|
| Re: Flattening out a table [message #587410 is a reply to message #587293] |
Fri, 14 June 2013 10:48  |
|
mxz25
Messages: 2
Registered: June 2013
|
Junior Member |
|
|
CREATE TABLE product_price
(
Prod_ID VARCHAR2(32) NOT NULL,
Prod_Prc_Cd VARCHAR2(10) NOT NULL,
Prod_Eff_dt DATE NOT NULL,
Prod_Prc NUMBER(13,5)
);
CREATE UNIQUE INDEX ux_product_price ON product_price
(Prod_ID, Prod_Prc_Cd, Prod_Eff_dt )
insert into product_price values ('3256', '11', '20-NOV-2011', 23.46);
insert into product_price values ('3256', '12', '22-DEC-2012', 25.06);
insert into product_price values ('3256', '15', '11-MAR-2013', 34.61);
insert into product_price values ('4353', '11', '10-OCT-2012', 45.26);
insert into product_price values ('4353', '15', '01-JUN-2013', 21.12);
insert into product_price values ('3256', '12', '20-FEB-2013', 44.11);
|
|
|
|
|
|
| Re: Flattening out a table [message #645201 is a reply to message #587410] |
Sun, 29 November 2015 09:53 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Solution (fixing the last insert):
SQL> with
2 data as (
3 select prod_id, prod_prc_cd, prod_eff_dt, prod_prc,
4 row_number() over (partition by prod_id order by prod_eff_dt) rn
5 from product_price
6 )
7 select prod_id,
8 max(decode(rn, 1,prod_prc)) app,
9 max(decode(rn, 1,prod_eff_dt)) app_eff_dt,
10 max(decode(rn, 2,prod_prc)) whp,
11 max(decode(rn, 2,prod_eff_dt)) whp_eff_dt,
12 max(decode(rn, 3,prod_prc)) drp,
13 max(decode(rn, 3,prod_eff_dt)) drp_eff_dt
14 from data
15 group by prod_id
16 order by prod_id
17 /
PROD_ID APP APP_EFF_DT WHP WHP_EFF_DT DRP DRP_EFF_DT
------- ---------- ----------- ---------- ----------- ---------- -----------
3256 23.46 20-NOV-2011 25.06 22-DEC-2012 34.61 11-MAR-2013
4353 45.26 10-OCT-2012 21.12 01-JUN-2013
5612 44.11 20-FEB-2013
|
|
|
|
| Re: Flattening out a table [message #645205 is a reply to message #645201] |
Sun, 29 November 2015 11:24 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Or simpler with just analytics:
SQL> with t as (
2 select prod_id,
3 prod_prc app,
4 prod_eff_dt app_eff_dt,
5 lead(prod_prc) over(partition by prod_id order by prod_eff_dt) whp,
6 lead(prod_eff_dt) over(partition by prod_id order by prod_eff_dt) whp_eff_dt,
7 lead(prod_prc,2) over(partition by prod_id order by prod_eff_dt) drp,
8 lead(prod_eff_dt,2) over(partition by prod_id order by prod_eff_dt) drp_eff_dt,
9 row_number() over(partition by prod_id order by prod_eff_dt) rn
10 from product_price
11 )
12 select prod_id,
13 app,
14 app_eff_dt,
15 whp,
16 whp_eff_dt,
17 drp,
18 drp_eff_dt
19 from t
20 where rn = 1
21 order by prod_id
22 /
PROD_ID APP APP_EFF_D WHP WHP_EFF_D DRP DRP_EFF_D
-------------------------------- ---------- --------- ---------- --------- ---------- ---------
3256 23.46 20-NOV-11 25.06 22-DEC-12 34.61 11-MAR-13
4353 45.26 10-OCT-12 21.12 01-JUN-13
5612 44.11 20-FEB-13
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 08:35:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
