Forum Stats

  • 3,767,862 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

Transpose Column Oracle Sql

User_3ZTH7
User_3ZTH7 Member Posts: 5 Green Ribbon

Hello All,

We have an oracle table in the below structure.

Table Name: poolagreements 

The requirement is to create a sql to generate report in the below format. As you can see, the last 6 months exposure should get transpose to rows. There might be cases where we have a year and month data missing as well which needs to be handled. I am trying with lag function without any luck, Appreciate if you check and suggest.



Thanks,

Pradeep.

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond
    Accepted Answer

    Hi,

    Presenting data from N rows as N columns on the same row is called Pivoting. Here's one way to do it:

    WITH  data_to_pivot  AS
    (
      SELECT  p.*
      ,	  DENSE_RANK () OVER ( ORDER BY rptyear	DESC
    			     ,	  rptmonth	DESC
      	  	    	    ) AS month_num
      FROM  poolagreements p
    -- WHERE  ...	 -- If you need any filtering, put it here
    )
    SELECT   MAX (rptyear)							AS rptyear
    ,	 MAX (rptmonth) KEEP (DENSE_RANK LAST ORDER BY rptyear)	AS rptmonth
    ,	 MAX (rundate)							AS rundate
    ,	 dealid
    ,	 SUM (CASE WHEN month_num = 1 THEN exposure END)		AS exposure
    ,	 SUM (CASE WHEN month_num = 2 THEN exposure END)		AS exposureminus1
    ,	 SUM (CASE WHEN month_num = 3 THEN exposure END)		AS exposureminus2
    ,	 SUM (CASE WHEN month_num = 4 THEN exposure END)		AS exposureminus3
    ,	 SUM (CASE WHEN month_num = 5 THEN exposure END)		AS exposureminus4
    ,	 SUM (CASE WHEN month_num = 6 THEN exposure END)		AS exposureminus5
    ,	 SUM (CASE WHEN month_num = 7 THEN exposure END)		AS exposureminus6
    FROM	 data_to_pivot
    GROUP BY dealid
    ORDER BY dealid -- or whatever you want
    ;
    

    The results I get from the sample data you posted (after correcting the bad TIMESTAMPs) are:


                                                   EXPO   EXPO   EXPO   EXPO   EXPO   EXPO
                                                   SURE   SURE   SURE   SURE   SURE   SURE
     RPT  RPT                              EXPO   MINUS  MINUS  MINUS  MINUS   MINUS MINUS
     YEAR MONTH RUNDATE              DEAL  SURE       1      2      3      4      5      6
    ----- ----- -------------------- ---- ------ ------ ------ ------ ------ ------ ------
     2021   6 29-06-2021 05.59.25    1256     65     70     80     90    100
     2021   6 29-06-2021 05.59.25    135      65     70     80     90    100    110    115
    

    These are not the results you posted. There is no dealid='801' in the posted sample data.

    Information about dates should be in DATE columns, not NUMBERs. This would be simpler if you used a DATE for the report month and year, rather than two NUMBERs.


    Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

    That was released 16 years ago. It's too bad you're using such old software. Oracle introduced a SELECT ... PIVOT feature, designed for just this kind of problem, in release 11.1.

    User_3ZTH7
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond
    edited Jul 1, 2021 10:24AM Accepted Answer

    during testing we noticed that in few scenarios the run date is same for several months.

    Then use:

    with t as (
               select  p.*,
                       to_date(rptyear || '/' || rptmonth,'yyyy/mm') rptdt
                 from  poolagreements p
              )
    select  to_char(sysdate,'yyyy') rptyear,
            to_char(sysdate,'fmmm') rptmonth,
            max(rundate) rundate,
            dealid,
            sum(
                case
                  when rptdt = trunc(sysdate,'mm') then exposure
                end
               ) exposure,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-1) then exposure
                end
               ) exposure1,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-2) then exposure
                end
               ) exposure2,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-3) then exposure
                end
               ) exposure3,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-4) then exposure
                end
               ) exposure4,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-5) then exposure
                end
               ) exposure5,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-6) then exposure
                end
               ) exposure6
      from  t
      where rptdt >= add_months(trunc(sysdate,'mm'),-6)
        and rptdt <=  trunc(sysdate,'mm')
      group by dealid
    /
    

    Keep in ming, today is July 1, so current + 6 previous months will now be Jan 21 - Jul 21 an the above query will return:

    RPTY RP RUNDATE                      DEAL EXPOSURE  EXPOSURE1  EXPOSURE2  EXPOSURE3  EXPOSURE4  EXPOSURE5  EXPOSURE6
    ---- -- ---------------------------- ---- -------- ---------- ---------- ---------- ---------- ---------- ----------
    2021 7  29-JUN-21 05.59.25.000000 AM 135                   65         70         80         90        100        110
    2021 7  28-MAY-21 05.00.25.000000 AM 801                              70         80         90
    2021 7  29-JUN-21 05.59.25.000000 AM 1256                  65         70         80         90        100
    
    SQL>
    

    SY.

    User_3ZTH7

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    HI, @User_3ZTH7

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

  • User_3ZTH7
    User_3ZTH7 Member Posts: 5 Green Ribbon

    Hi Frank,


    Thanks. Updated with oracle version, test data and commands to create table and insert test data.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond

    Hi,

    Thanks for posting the sample data, but please don't make changes to your messages, especially after people have replied to them. It makes the thread hard to read, and your changes easy to miss. Post all corrections or additions in a new message.

    INSERT ALL

     INTO poolagreements (RPTYear, RPTMonth, RunDate, DealID, Exposure) VALUES (2021, 6, TIMESTAMP '29-06-2021 05:59:25', '135', 65)

    Would you like to get answers that work? Make sure the statements you post for the sample data works, too. Test (and, if necessary, fix) your statments before you post them. Review how TIMESTAMP literals work. If you want to use a format like 'DD-MM-YYYY', then use the TO_TIMESTAMP function.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,202 Red Diamond
    Accepted Answer

    Hi,

    Presenting data from N rows as N columns on the same row is called Pivoting. Here's one way to do it:

    WITH  data_to_pivot  AS
    (
      SELECT  p.*
      ,	  DENSE_RANK () OVER ( ORDER BY rptyear	DESC
    			     ,	  rptmonth	DESC
      	  	    	    ) AS month_num
      FROM  poolagreements p
    -- WHERE  ...	 -- If you need any filtering, put it here
    )
    SELECT   MAX (rptyear)							AS rptyear
    ,	 MAX (rptmonth) KEEP (DENSE_RANK LAST ORDER BY rptyear)	AS rptmonth
    ,	 MAX (rundate)							AS rundate
    ,	 dealid
    ,	 SUM (CASE WHEN month_num = 1 THEN exposure END)		AS exposure
    ,	 SUM (CASE WHEN month_num = 2 THEN exposure END)		AS exposureminus1
    ,	 SUM (CASE WHEN month_num = 3 THEN exposure END)		AS exposureminus2
    ,	 SUM (CASE WHEN month_num = 4 THEN exposure END)		AS exposureminus3
    ,	 SUM (CASE WHEN month_num = 5 THEN exposure END)		AS exposureminus4
    ,	 SUM (CASE WHEN month_num = 6 THEN exposure END)		AS exposureminus5
    ,	 SUM (CASE WHEN month_num = 7 THEN exposure END)		AS exposureminus6
    FROM	 data_to_pivot
    GROUP BY dealid
    ORDER BY dealid -- or whatever you want
    ;
    

    The results I get from the sample data you posted (after correcting the bad TIMESTAMPs) are:


                                                   EXPO   EXPO   EXPO   EXPO   EXPO   EXPO
                                                   SURE   SURE   SURE   SURE   SURE   SURE
     RPT  RPT                              EXPO   MINUS  MINUS  MINUS  MINUS   MINUS MINUS
     YEAR MONTH RUNDATE              DEAL  SURE       1      2      3      4      5      6
    ----- ----- -------------------- ---- ------ ------ ------ ------ ------ ------ ------
     2021   6 29-06-2021 05.59.25    1256     65     70     80     90    100
     2021   6 29-06-2021 05.59.25    135      65     70     80     90    100    110    115
    

    These are not the results you posted. There is no dealid='801' in the posted sample data.

    Information about dates should be in DATE columns, not NUMBERs. This would be simpler if you used a DATE for the report month and year, rather than two NUMBERs.


    Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

    That was released 16 years ago. It's too bad you're using such old software. Oracle introduced a SELECT ... PIVOT feature, designed for just this kind of problem, in release 11.1.

    User_3ZTH7
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond
    edited Jun 30, 2021 12:23PM

    As you can see, the last 6 months exposure should get transpose to rows. 

    I see 7 months Dec 20 - June 21. I will assume you want current month + previous 6 months:

    select  to_char(sysdate,'yyyy') rptyear,
            to_char(sysdate,'fmmm') rptmonth,
            max(rundate) rundate,
            dealid,
            sum(
                case
                  when rundate >= trunc(sysdate,'mm') and rundate < add_months(trunc(sysdate,'mm'),1) then exposure
                end
               ) exposure,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-1) and rundate < trunc(sysdate,'mm') then exposure
                end
               ) exposure1,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-2) and rundate < add_months(trunc(sysdate,'mm'),-1) then exposure
                end
               ) exposure2,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-3) and rundate < add_months(trunc(sysdate,'mm'),-2) then exposure
                end
               ) exposure3,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-4) and rundate < add_months(trunc(sysdate,'mm'),-3) then exposure
                end
               ) exposure4,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-5) and rundate < add_months(trunc(sysdate,'mm'),-4) then exposure
                end
               ) exposure5,
            sum(
                case
                  when rundate >= add_months(trunc(sysdate,'mm'),-6) and rundate < add_months(trunc(sysdate,'mm'),-5) then exposure
                end
               ) exposure6
      from  poolagreements
      where rundate >= add_months(trunc(sysdate,'mm'),-6)
        and rundate <  add_months(trunc(sysdate,'mm'),1)
      group by dealid
    /
    
    RPTY RP RUNDATE                      DEALID EXPOSURE EXPOSURE1 EXPOSURE2 EXPOSURE3 EXPOSURE4 EXPOSURE5 EXPOSURE6
    ---- -- ---------------------------- ------ -------- --------- --------- --------- --------- --------- ---------
    2021 6  29-JUN-21 05.59.25.000000 AM 135          65        70        80        90       100       110       115
    2021 6  28-MAY-21 05.00.25.000000 AM 801                    70        80        90
    2021 6  29-JUN-21 05.59.25.000000 AM 1256         65        70        80        90       100
    SQL>
    

    SY.

    User_3ZTH7
  • User_3ZTH7
    User_3ZTH7 Member Posts: 5 Green Ribbon

    Thanks a lot Solomon. This is working as expected but during testing we noticed that in few scenarios the run date is same for several months. For ex: May and June 2021 has same run date 27/06/2021. In these cases the exposure get summed up.

    It's my bad, I haven't noticed this initially. Now trying to construct a date using reporting year and month columns and trying to use this in the select clause for validation instead rundate.

    For ex; for June 2021 it is 01-06-2021. Trying to create a similar date field for each month and year and keeping the date as 1st by default. Appreciate if you suggest for the same.

    Thanks a lot again.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,900 Red Diamond
    edited Jul 1, 2021 10:24AM Accepted Answer

    during testing we noticed that in few scenarios the run date is same for several months.

    Then use:

    with t as (
               select  p.*,
                       to_date(rptyear || '/' || rptmonth,'yyyy/mm') rptdt
                 from  poolagreements p
              )
    select  to_char(sysdate,'yyyy') rptyear,
            to_char(sysdate,'fmmm') rptmonth,
            max(rundate) rundate,
            dealid,
            sum(
                case
                  when rptdt = trunc(sysdate,'mm') then exposure
                end
               ) exposure,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-1) then exposure
                end
               ) exposure1,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-2) then exposure
                end
               ) exposure2,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-3) then exposure
                end
               ) exposure3,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-4) then exposure
                end
               ) exposure4,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-5) then exposure
                end
               ) exposure5,
            sum(
                case
                  when rptdt = add_months(trunc(sysdate,'mm'),-6) then exposure
                end
               ) exposure6
      from  t
      where rptdt >= add_months(trunc(sysdate,'mm'),-6)
        and rptdt <=  trunc(sysdate,'mm')
      group by dealid
    /
    

    Keep in ming, today is July 1, so current + 6 previous months will now be Jan 21 - Jul 21 an the above query will return:

    RPTY RP RUNDATE                      DEAL EXPOSURE  EXPOSURE1  EXPOSURE2  EXPOSURE3  EXPOSURE4  EXPOSURE5  EXPOSURE6
    ---- -- ---------------------------- ---- -------- ---------- ---------- ---------- ---------- ---------- ----------
    2021 7  29-JUN-21 05.59.25.000000 AM 135                   65         70         80         90        100        110
    2021 7  28-MAY-21 05.00.25.000000 AM 801                              70         80         90
    2021 7  29-JUN-21 05.59.25.000000 AM 1256                  65         70         80         90        100
    
    SQL>
    

    SY.

    User_3ZTH7