CODEWITHSUNDEEP
sqloracle-databaseunpivot
JagaSrik
JagaSrik

Reputation: 718

How to do something like unpivot like in oracle sql for the following scenario?

This is the the table statements, i want to convert the data in something unpivot like using just sql.

create table galy(
pro_Date date,
profit number
);
insert into galy values(sysdate-1,30);
insert into galy values(sysdate,20);
insert into galy values(sysdate+1,40);
insert into galy values(sysdate+2,50);
insert into galy values(sysdate+3,70);
commit;

Output Needed in a single query (dates having sat and sunday should not be considered)

      Date                  d-1                    d                d+1
    01-02-2018(sysdate)      30                    20                40
     ....
     ....

Upvotes: 0

Views: 111

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6084

A pivot/unpivot is not needed to get the data to display in the format you want. As long as there is only 1 PRO_DATE per day, you can use the LEAD and LAG analytic functions.

Query

SELECT pro_date,
       LAG (profit) OVER (ORDER BY pro_date)     AS prior_day_profit,
       profit                                    AS current_day_profit,
       LEAD (profit) OVER (ORDER BY pro_date)     AS next_day_profit
  FROM galy;

Result

    PRO_DATE    PRIOR_DAY_PROFIT    CURRENT_DAY_PROFIT    NEXT_DAY_PROFIT
____________ ___________________ _____________________ __________________
04-OCT-20                                           30                 20
05-OCT-20                     30                    20                 40
06-OCT-20                     20                    40                 50
07-OCT-20                     40                    50                 70
08-OCT-20                     50                    70

Update

To exclude weekends, you can simply add a where clause to your query

SELECT pro_date,
       LAG (profit) OVER (ORDER BY pro_date)      AS prior_day_profit,
       profit                                     AS current_day_profit,
       LEAD (profit) OVER (ORDER BY pro_date)     AS next_day_profit
  FROM galy
 WHERE TO_CHAR (pro_date, 'DY') NOT IN ('SAT', 'SUN');

Upvotes: 1

Related Questions