CODEWITHSUNDEEP
sqloracle-database
user3024635
user3024635

Reputation: 89

Convert columns to rows oracle version 1.5.5 based on dates

I have my sample table as below:

Date            Product  Member_code
01/01/2015      ABC       1A
01/02/2016      BBB       1A
12/04/2013      SCC       2C
12/04/2014      AQQ       2C

Expected Result

Member_code   Old_product       New_product
 1A             ABC               BBB
 2C             SCC               AQQ

thanks for your help in advance cheers

Upvotes: 0

Views: 74

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

One way is to use SELF JOIN.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE yourtable
    (p_Date varchar2(10), Product varchar2(3), Member_code varchar2(2))
;

INSERT ALL 
    INTO yourtable (p_Date, Product, Member_code)
         VALUES ('2015-01-01', 'ABC', '1A')
    INTO yourtable (p_Date, Product, Member_code)
         VALUES ('2016-02-01', 'BBB', '1A')
    INTO yourtable (p_Date, Product, Member_code)
         VALUES ('2013-04-12', 'SCC', '2C')
    INTO yourtable (p_Date, Product, Member_code)
         VALUES ('2014-04-12', 'AQQ', '2C')
SELECT * FROM dual
;

Query 1:

 SELECT t1.member_code,
       t1.product old_product,
       t2.product new_product
FROM   yourtable t1
       join yourtable t2
         ON t1.member_code = t2.member_code
            AND t1.p_date < t2.p_date

Results:

| MEMBER_CODE | OLD_PRODUCT | NEW_PRODUCT |
|-------------|-------------|-------------|
|          1A |         ABC |         BBB |
|          2C |         SCC |         AQQ |

Upvotes: 1

user5683823
user5683823

Reputation:

The most efficient way to capture the oldest and the newest values in the product column is with the first / last aggregate function. (Personal observation: there are many practitioners, even advanced ones, who avoid this aggregate function - for reasons I don't understand.)

Using Kaushik Nayak's setup (CREATE TABLE and INSERT statements):

select   member_code,
         min(product) keep (dense_rank first order by p_date) as old_product,
         min(product) keep (dense_rank last  order by p_date) as new_product
from     yourtable
group by member_code
;

MEMBER_CODE  OLD_PRODUCT  NEW_PRODUCT
-----------  -----------  -----------
1A           ABC          BBB
2C           SCC          AQQ

Upvotes: 2

Related Questions