Transpose Column Oracle Sql

User_3ZTH7 · Jun 30, 2021 10:10AM

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.

Frank Kulash · Jun 30, 2021 12:14PM

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.

Solomon Yakobson · Jul 1, 2021 10:22AM

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.

Frank Kulash · Jun 30, 2021 10:25AM

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 · Jun 30, 2021 11:07AM

https://community.oracle.com/tech/developers/discussion/comment/16800623#Comment_16800623

Hi Frank,

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

Frank Kulash · Jun 30, 2021 11:34AM

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 · Jun 30, 2021 12:14PM

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.

Solomon Yakobson · Jun 30, 2021 12:19PM

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 · Jul 1, 2021 6:19AM

https://community.oracle.com/tech/developers/discussion/comment/16800638#Comment_16800638

Thanks a lot Frank, this is working as expected.

User_3ZTH7 · Jul 1, 2021 6:29AM

https://community.oracle.com/tech/developers/discussion/comment/16800640#Comment_16800640

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 · Jul 1, 2021 10:22AM

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 · Jul 5, 2021 2:08PM

https://community.oracle.com/tech/developers/discussion/comment/16800753#Comment_16800753

Thanks Solomon. Much Appreciated.

Forum Stats

Quick Links

Categories

Transpose Column Oracle Sql

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Transpose Column Oracle Sql

Best Answers

Answers