Home » SQL & PL/SQL » SQL & PL/SQL » transpose rows into columns (oracle)
transpose rows into columns [message #684936] Thu, 30 September 2021 10:26 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Team,

I am trying to get the rows into columns but couldn't get the proper results.
Below is the sample data created expalining the issue.

CREATE TABLE PYMNT_HEADER_TBL
(PAYMENT_ID INT NOT NULL, DESCR VARCHAR2(50), START_DATE DATE NOT NULL, CLOSE_DATE DATE NOT NULL)
INSERT INTO PYMNT_HEADER_TBL
VALUES (56134, 'payment details', to_date('01/01/2019', 'MM/DD/YYYY'),  to_date('12/31/2025', 'MM/DD/YYYY'))
CREATE TABLE PAYMENT_TBL
(PAYMENT_ID INT NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, AMOUNT INT NOT NULL)
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2019', 'MM/DD/YYYY'),  
TO_DATE('12/31/2019', 'MM/DD/YYYY'),  100);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2020', 'MM/DD/YYYY'),  
TO_DATE('12/31/2020', 'MM/DD/YYYY'),  200);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2021', 'MM/DD/YYYY'),  
TO_DATE('12/31/2021', 'MM/DD/YYYY'),  300);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2022', 'MM/DD/YYYY'),  
TO_DATE('12/31/2022', 'MM/DD/YYYY'),  400);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2023', 'MM/DD/YYYY'),  
TO_DATE('12/31/2023', 'MM/DD/YYYY'),  500);
I have prepaed below sql but results are not as expected.

SELECT A.PAYMENT_ID, A.START_DATE, A.CLOSE_DATE,
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
      EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT 
     WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR1",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT 
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR2",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT 
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR3",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT 
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR4",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT 
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN 
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR5"
FROM PYMNT_HEADER_TBL A, PAYMENT_TBL B
WHERE A.PAYMENT_ID = 56134
AND A.PAYMENT_ID = B.PAYMENT_ID
Output from above SQL query as below:
PAYMENT_ID     START_DATE	CLOSE_DATE	YEAR1	YEAR2	YEAR3	YEAR4	YEAR5
56134	       01-JAN-19	31-DEC-25	1200	1200	1200	1200	1200
56134	       01-JAN-19	31-DEC-25	2400	2400	2400	2400	2400
56134	       01-JAN-19	31-DEC-25	3600	3600	3600	3600	3600
56134	       01-JAN-19	31-DEC-25	4800	4800	4800	4800	4800
56134	       01-JAN-19	31-DEC-25	6000	6000	6000	6000	6000
But we need the output as below. Request please help me with the correct sql.

PAYMENT_ID   START_DATE    CLOSE_DATE      YERA1      YEAR2     YEAR3     YEAR4      YEAR5
56134        01/01/2019    12/31/2025    1200        2400      3600      4800       6000 

Appreciate you response on this. Thank you.

Regards
Suji
Re: transpose rows into columns [message #684937 is a reply to message #684936] Thu, 30 September 2021 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But we need the output as below.
Which represents what?
In other words, explain, with words, what are the result columns values. How they are computed.
Don't expect we can guess what it should be from a query that does not give the correct result.

Re: transpose rows into columns [message #684939 is a reply to message #684937] Thu, 30 September 2021 19:09 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Lets consider below table and based on the close date in PYMNT_HEADER_TBL w.r.t to start in PAYMENT_TBL, display the years in columns for years (year1, year2, year3, year4 and year5) and populate years column with number of months times amount. and we should have only one row per payment id.

PAYMENT_ID	START_DATE	END_DATE	AMOUNT
56134	        01-JAN-19	31-DEC-19	100
56134	        01-JAN-20	31-DEC-20	200
56134	        01-JAN-21	31-DEC-21	300
56134	        01-JAN-22	31-DEC-22	400
56134	        01-JAN-23	31-DEC-23	500
Thank you.

Regards
Suji
Re: transpose rows into columns [message #684941 is a reply to message #684939] Fri, 01 October 2021 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
we should have only one row per payment id.

So post data for several payment id we can verify our query fit this requirement.

Can a row in PAYMENT_TBL cover several years?
Can there be several rows in PAYMENT_TBL for the same year and same payment id?
If so, post INSERT statements for all the different cases.

Re: transpose rows into columns [message #684942 is a reply to message #684941] Fri, 01 October 2021 01:26 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Please see my comments on your questions.
Can a row in PAYMENT_TBL cover several years? -- there won't be server years.. max 5 or 6 years
Can there be several rows in PAYMENT_TBL for the same year and same payment id?... there will be only one row for year.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #684943 is a reply to message #684942] Fri, 01 October 2021 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can a row in PAYMENT_TBL cover several years? -- there won't be server years.. max 5 or 6 years
This does not answer my question.
in addition, "there won't be [server]several years.. max 5 or 6 years" is meaningless, aren't 5 or 6 years several years?


Quote:
there will be only one row for year.
Is this at least and at most one?
Can there be some months or years without payment?

In short, can there be some cases in PAYMENT_TBL like these, for a PYMNT_HEADER_TBL (start,end) as (01/01/2000,01/31/2003):
01/01/2000->03/31/2000
<no payment in months 4 and 5)
06/01/2000->06/15/2000
06/16/2000->06/30/2000
07/01/2000->08/20/2000
<no payment between 08/21 and 08/31
09/01/2000->09/30/2000
<no payment in months 10 to 12)
<no payment in 2001>
<no payment in 2002 until 12/01>
12/01/2002->01/31/2003

Post one answer per line.

Post the added INSERT statements I asked.

[Updated on: Fri, 01 October 2021 01:55]

Report message to a moderator

Re: transpose rows into columns [message #684946 is a reply to message #684943] Fri, 01 October 2021 04:08 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

There could be the scenario like start_date and end_date in PAYMENT_TBL as '06/01/2000' and '10/31/2022'.
In this case Year1 would be (7 months * amount), year2 would be (12 months * amount), year3 would be (10 months * amount) and year4, year5 would be blank.

There won't be such scenario like missing months in between.

INSERT INTO PYMNT_HEADER_TBL
VALUES (75200, 'payment details_2', to_date('01/01/2020', 'MM/DD/YYYY'),  to_date('10/31/2022', 'MM/DD/YYYY'));
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (75200, to_date('06/01/2020', 'MM/DD/YYYY'),  
TO_DATE('10/31/2022', 'MM/DD/YYYY'),  700);
Thank you.

Regards
Suji

Re: transpose rows into columns [message #684947 is a reply to message #684946] Fri, 01 October 2021 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To a clear understanding: there is, and this is the only case, exactly one line in PAYMENT_TBL per year between start and end dates in PYMNT_HEADER_TBL for each PAYMENT_ID.
Is this correct or not? (In this case, there is no need to have start and end dates in PAYMENT_TBL, a simply "year" column would be sufficient, and necessary.)

And post the INSERT statements I asked (the minimum is a second PAYMENT_ID set of rows).

Re: transpose rows into columns [message #684949 is a reply to message #684947] Fri, 01 October 2021 06:51 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

There would be always one row in PYMNT_HEADER_TBL for each PAYMENT_ID. And there could be multiple rows in PAYMENT_TBL for each PAYMENT_ID but END_DATE of PAYMENT_TBL is always on or before CLOSE_DATE of PYMNT_HEADER_TBL.
and there will be only one row for year.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #684950 is a reply to message #684949] Fri, 01 October 2021 07:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Something like this, assuming there will be no more than 10 year period:

WITH T1 AS (
            SELECT  YEAR_NUMBER,
                    PAYMENT_ID,
                    GREATEST(START_DATE,ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * (YEAR_NUMBER - 1))) START_DATE,
                    LEAST(CLOSE_DATE,ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * YEAR_NUMBER) - 1) CLOSE_DATE
              FROM  PYMNT_HEADER_TBL,
                    LATERAL(
                            SELECT  LEVEL YEAR_NUMBER
                              FROM  DUAL
                              CONNECT BY ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * (LEVEL - 1)) <= CLOSE_DATE
                           )
           ),
     T2 AS (
            SELECT  T1.YEAR_NUMBER,
                    T1.PAYMENT_ID,
                    GREATEST(P.START_DATE,T1.START_DATE) START_DATE,
                    LEAST(P.END_DATE,T1.CLOSE_DATE) END_DATE,
                    AMOUNT
              FROM  T1,
                    PAYMENT_TBL P
              WHERE P.START_DATE(+) <= T1.CLOSE_DATE
                AND P.END_DATE(+) >= T1.START_DATE
           )
SELECT  *
  FROM  T2
  PIVOT(
        SUM(AMOUNT * MONTHS_BETWEEN(END_DATE + 1,START_DATE))
        FOR YEAR_NUMBER IN (1 YEAR1,2 YEAR2,3 YEAR3,4 YEAR4,5 YEAR5,6 YEAR6,7 YEAR7,8 YEAR8,9 YEAR9,10 YEAR10)
       )
/

PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5      YEAR6      YEAR7      YEAR8      YEAR9     YEAR10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56134       1200       2400       3600       4800       6000

SQL>
SY.

[Updated on: Fri, 01 October 2021 07:53]

Report message to a moderator

Re: transpose rows into columns [message #684951 is a reply to message #684949] Fri, 01 October 2021 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1)
This is not exactly and a complete answer to what I asked.
Is it, and this is the only case, exactly one line in PAYMENT_TBL per year between start and end dates in PYMNT_HEADER_TBL for each PAYMENT_ID?
The answer is a simple "yes" or "no".

2)
If, in your latest example, "start_date and end_date in PAYMENT_TBL as '06/01/2000' and '10/31/20202'" then is there, between these 2 dates, only and exactly in PAYMENT_TBL, one row for 2000, one row for 2001 and one row for 2002?
Once again, the answer I expect it "yes" or "no".

If the answer is "yes" for both questions (which are in fact the same one), I have 2 more questions (still with your latest example):
3) Can start_date in PAYMENT_TBL for year 2000 be after start_date in PYMNT_HEADER_TBL?
4) Can end_date in PAYMENT_TBL for year 2002 be before close_date in PYMNT_HEADER_TBL?

Expected answers:
1) yes/no
2) yes/no
3) yes/no/na (not applicable if answer in previous points is "no")
4) yes/no/na (not applicable if answer in previous points is "no")

Re: transpose rows into columns [message #684953 is a reply to message #684951] Fri, 01 October 2021 09:52 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Below is the my response on your queries:
1) Yes
2) No
3) Yes

hope you are clear on this.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #684954 is a reply to message #684950] Fri, 01 October 2021 09:53 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank you Solomon for the solution.
I am trying to understand the sql query that you provided here.

Regards
Suji

[Updated on: Fri, 01 October 2021 09:54]

Report message to a moderator

Re: transpose rows into columns [message #684955 is a reply to message #684953] Fri, 01 October 2021 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
suji6281 wrote on Fri, 01 October 2021 16:52
Hi Michel,

Below is the my response on your queries:
1) Yes
2) No
3) Yes

hope you are clear on this.

Thank you.

Regards
Suji

What about 4)??? Sad

Note that Solomon's query may not work with your Oracle version, this is why we ask:


Michel Cadot wrote on Tue, 16 October 2018 10:35

...
- give your Oracle version
...
cookiemonster wrote on Fri, 01 March 2019 11:43
...
3) What oracle version are you using?
cookiemonster wrote on Fri, 01 March 2019 12:29
...
What is the exact oracle version? run select * from v$version if you're not sure.

[Updated on: Sat, 02 October 2021 03:04]

Report message to a moderator

Re: transpose rows into columns [message #684956 is a reply to message #684955] Fri, 01 October 2021 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another query is (assuming there is only 1 row per payment_id in PYMNT_HEADER_TBL, and assuming that dates in tables start at day 1 and end at last day of the month):
SQL> with
  2    data as (
  3      select p.payment_id, p.amount,
  4             greatest(p.start_date,h.start_date) start_date,
  5             least(p.end_date, h.close_date) end_date,
  6             trunc(months_between(p.start_date,h.start_date)/12)+1 year
  7      from PAYMENT_TBL p, PYMNT_HEADER_TBL h
  8      where p.payment_id = h.payment_id
  9        -- conditions to be sure used data in PAYMENT_TBL are correct:
 10        and p.start_date >= h.start_date
 11        and p.end_date <= h.close_date
 12    )
 13  select *
 14  from data
 15       pivot (
 16         sum(amount*months_between(end_date+1,start_date))
 17         for year in (1 "YEAR1", 2 "YEAR2", 3 "YEAR3", 4 "YEAR4", 5 "YEAR5")
 18       )
 19  order by 1
 20  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5
---------- ---------- ---------- ---------- ---------- ----------
     56134       1200       2400       3600       4800       6000

[Updated on: Fri, 01 October 2021 11:30]

Report message to a moderator

Re: transpose rows into columns [message #684957 is a reply to message #684956] Fri, 01 October 2021 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Removing the second assumption:
SQL> with
  2    data as (
  3      select p.payment_id, p.amount,
  4             trunc(greatest(p.start_date,h.start_date),'MONTH') start_date,
  5             last_day(least(p.end_date, h.close_date)) end_date,
  6             trunc(months_between(trunc(p.start_date,'MONTH'),
  7                                  trunc(h.start_date,'MONTH'))
  8                   /12) + 1 year
  9      from PAYMENT_TBL p, PYMNT_HEADER_TBL h
 10      where p.payment_id = h.payment_id
 11        -- conditions to be sure used data in PAYMENT_TBL are correct:
 12        and p.start_date >= h.start_date
 13        and p.end_date <= h.close_date
 14    )
 15  select *
 16  from data
 17       pivot (
 18         sum(amount*months_between(end_date+1,start_date))
 19         for year in (1 "YEAR1", 2 "YEAR2", 3 "YEAR3", 4 "YEAR4", 5 "YEAR5")
 20       )
 21  order by 1
 22  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5
---------- ---------- ---------- ---------- ---------- ----------
     56134       1200       2400       3600       4800       6000
Re: transpose rows into columns [message #684958 is a reply to message #684954] Fri, 01 October 2021 11:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
suji6281 wrote on Fri, 01 October 2021 10:53
I am trying to understand the sql query that you provided here.
T1 - split each PYMNT_HEADER_TBL PAYMENT_ID by year and assign consecutive numbers to each year within PAYMENT_ID (starting with 1).
T2 - outer join T1 to PAYMENT_TBL by PAYMENT_ID and year and pivot by year number (up to 10 consecutive years). IMPORTANT: my solution assumes PAYMENT_TBL START_DATE - END_DATE intervals don't span across multiple years (otherwise we would have to split PAYMENT_TBL by year also same way T1 does).

SY.
Re: transpose rows into columns [message #684994 is a reply to message #684953] Tue, 05 October 2021 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: transpose rows into columns [message #685000 is a reply to message #684994] Wed, 06 October 2021 08:46 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel/Solomon,

Thanks for the SQL Query and testing this with different scenarios. I will get back to you if required any further information from you.

Regards
Suji
Re: transpose rows into columns [message #685047 is a reply to message #685000] Wed, 13 October 2021 14:11 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel/Solomon,

The requirement was changed little bit.
If there was 6 years payment that was started in Aug 2021 then we would expect to see 5 months in 2021, 12 months in 2022,
12 months in 2023, 12 months in 2024, 12 months in 2025 and remaining 7 months in hereafter column and sum of all year amounts.
Consider least date of PAYMENT_TBL would be START_DATE and greatest date of PAYMENT_TBL would be END_DATE.

CREATE TABLE PAYMENT_TBL
(PAYMENT_ID INT NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, AMOUNT INT NOT NULL, NUMBER_OF_PAYMENTS INT NOT NULL)
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT, NUMBER_OF_PAYMENTS) VALUES (56100, to_date('08/01/2021', 'MM/DD/YYYY'),  
TO_DATE('08/31/2021', 'MM/DD/YYYY'),  100, 1);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT, NUMBER_OF_PAYMENTS) VALUES (56100, to_date('09/01/2021', 'MM/DD/YYYY'),  
TO_DATE('07/31/2026', 'MM/DD/YYYY'),  100, 59);
CREATE TABLE PAYMENT_SCHEDULE_TBL
(PAYMENT_ID INT NOT NULL, PERIOD_NAME VARCHAR2(50), AMOUNT INT NOT NULL)
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2021', 100);

INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2023', 100);

INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2023', 100);

INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2024', 100);


INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2025', 100);


INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2026', 100);
Expected output would be as below. Request please help me with the correct sql.

PAYMENT_ID   START_DATE    END_DATE      YERA1      YEAR2     YEAR3     YEAR4      YEAR5      THEREAFTER      TOTAL
56100        08/01/2021    07/31/2026    500        1200      1200      1200       1200       700             6000


Re: transpose rows into columns [message #685050 is a reply to message #685047] Thu, 14 October 2021 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

  • How does "NUMBER_OF_PAYMENTS" come into play in your requirements?
  • Hos does "AMOUNT" in PAYMENT_TBL come into play in your requirements when there is also an "AMOUNT" column in PAYMENT_SCHEDULE_TBL? Which one has to be taken?
  • There are 2 lines in PAYMENT_TBL, why there is only 1 in your result?
3 points, 3 answers, please.

Re: transpose rows into columns [message #685055 is a reply to message #685050] Thu, 14 October 2021 06:46 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Thanks for the response.
We can consider either of table and to retrieve the output as required.
If NUMBER_OF_PAYMENTS field makes our query easy to get YEAR1, YERA2, YEAR3, YEAR4, YEAR5, THEREAFTER columns then we can use it or else we can go with the other table data.
And sometimes payment table is having 2 lines with payment type as 'Advanced' and 'Recurring', but I didn't include that field here since it may confuse you.
but most of other PAYMENT_IDs has single line in PAYMENT_TBL.

Hope I cleared you doubts.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #685057 is a reply to message #685055] Thu, 14 October 2021 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So, we can just consider PAYMENT_TBL as it contains all data we need for your report.
Are those, possible, 2 lines in PAYMENT_TBL always consecutive? Here one ends on 08/31/2021 and the other one starts on 09/01/2021, is this always the case (I means 2nd_start_date = 1st_end_date+1)?

Re: transpose rows into columns [message #685059 is a reply to message #685057] Thu, 14 October 2021 19:20 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

You can consider any table that simplifies our logic.
And yes PAYMENT_TBL always has consecutive dates.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #685060 is a reply to message #685059] Fri, 15 October 2021 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, in this case here's one possibility using only payment_schedule_tbl:
SQL> with
  2    data as (
  3      select payment_id,
  4             extract(year
  5               from to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')) yr,
  6             min(extract(year
  7                   from to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')))
  8               over (partition by payment_id) minyr,
  9             amount
 10      from payment_schedule_tbl
 11    )
 12  select payment_id,
 13         sum(case when yr = minyr+0 then amount end) year1,
 14         sum(case when yr = minyr+1 then amount end) year2,
 15         sum(case when yr = minyr+2 then amount end) year3,
 16         sum(case when yr = minyr+3 then amount end) year4,
 17         sum(case when yr = minyr+4 then amount end) year5,
 18         sum(case when yr > minyr+4 then amount end) thereafter,
 19         sum(amount) total
 20  from data
 21  group by payment_id, minyr
 22  order by payment_id
 23  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100        500       1200       1200       1200       1200        700       6000
Re: transpose rows into columns [message #685061 is a reply to message #685060] Fri, 15 October 2021 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or, if you are sure about your date values (so without verifying it with to_date):
SQL> with
  2    data as (
  3      select payment_id,
  4             to_number(substr(period_name,5)) yr,
  5             to_number(min(substr(period_name,5)) over (partition by payment_id)) minyr,
  6             amount
  7      from payment_schedule_tbl
  8    )
  9  select payment_id,
 10         sum(case when yr = minyr+0 then amount end) year1,
 11         sum(case when yr = minyr+1 then amount end) year2,
 12         sum(case when yr = minyr+2 then amount end) year3,
 13         sum(case when yr = minyr+3 then amount end) year4,
 14         sum(case when yr = minyr+4 then amount end) year5,
 15         sum(case when yr > minyr+4 then amount end) thereafter,
 16         sum(amount) total
 17  from data
 18  group by payment_id, minyr
 19  order by payment_id
 20  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100        500       1200       1200       1200       1200        700       6000
Re: transpose rows into columns [message #685062 is a reply to message #685061] Fri, 15 October 2021 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And using only payment_tbl:
SQL> with
  2    data as (
  3      select payment_id, amount,
  4             extract(year from add_months(start_date,column_value-1)) yr,
  5             min(extract(year from add_months(start_date,column_value-1)))
  6               over (partition by payment_id) minyr
  7      from payment_tbl,
  8           table(cast(multiset(select level from dual
  9                               connect by level <= number_of_payments)
 10                 as sys.odciNumberList))
 11    )
 12  select payment_id,
 13         sum(case when yr = minyr+0 then amount end) year1,
 14         sum(case when yr = minyr+1 then amount end) year2,
 15         sum(case when yr = minyr+2 then amount end) year3,
 16         sum(case when yr = minyr+3 then amount end) year4,
 17         sum(case when yr = minyr+4 then amount end) year5,
 18         sum(case when yr > minyr+4 then amount end) thereafter,
 19         sum(amount) total
 20  from data
 21  group by payment_id, minyr
 22  order by payment_id
 23  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100        500       1200       1200       1200       1200        700       6000
Re: transpose rows into columns [message #685063 is a reply to message #685062] Fri, 15 October 2021 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many redundancies in your tables that should be always correct like "number_of_payments" and months between "start_date" and "end_date"+1) in "payment_tbl" or "number_of_payments" in "payment_tbl" and the actual number of rows in "payment_schedule_tbl"... They may be the origin of false results if some discrepancies exist.
You will say that they can't happen but Murphy' laws say that if this can happen then it will.

So, just for fun, I modified the query to verify the data at the same time it generates the report. In case of discrepancy it generates an error.

SQL> col "Discrepancy in payment_tbl" noprint
SQL> col "Inconsistency in tables"    noprint
SQL> with
  2    data as (
  3      select payment_id, amount, number_of_payments,
  4             months_between(end_date+1,start_date) number_of_months,
  5             extract(year from add_months(start_date,column_value-1)) yr,
  6             min(extract(year from add_months(start_date,column_value-1)))
  7               over (partition by payment_id) minyr,
  8             count(*) over
  9               (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
 10               nb_payment
 11      from payment_tbl,
 12           table(cast(multiset(select level from dual
 13                               connect by level <= number_of_payments)
 14                 as sys.odciNumberList))
 15    )
 16  select d.payment_id,
 17         sum(case when d.yr = d.minyr+0 then d.amount end) year1,
 18         sum(case when d.yr = d.minyr+1 then d.amount end) year2,
 19         sum(case when d.yr = d.minyr+2 then d.amount end) year3,
 20         sum(case when d.yr = d.minyr+3 then d.amount end) year4,
 21         sum(case when d.yr = d.minyr+4 then d.amount end) year5,
 22         sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
 23         sum(d.amount) total,
 24         max(case
 25               when    d.payment_id is null or p.payment_id is null
 26                    or p.nb_payment != d.nb_payment
 27                    or p.amount != d.amount
 28                 then 1/0 end) "Inconsistency in tables",
 29         max(case
 30               when d.number_of_payments != d.number_of_months
 31                 then 1/0 end) "Discrepancy in payment_tbl"
 32  from data d
 33         full outer join
 34       ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
 35         from payment_schedule_tbl
 36         group by payment_id, substr(period_name,5), amount ) p
 37         on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
 38  group by d.payment_id, d.minyr
 39  order by d.payment_id
 40  /
PAYMENT_ID      YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100        500       1200       1200       1200       1200        700       6000
Now modify "number_of_payments" to create a discrepancy:
SQL> update payment_tbl set number_of_payments = 2 where number_of_payments = 1;

1 row updated.

SQL> with
  2    data as (
  3      select payment_id, amount, number_of_payments,
  4             months_between(end_date+1,start_date) number_of_months,
  5             extract(year from add_months(start_date,column_value-1)) yr,
  6             min(extract(year from add_months(start_date,column_value-1)))
  7               over (partition by payment_id) minyr,
  8             count(*) over
  9               (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
 10               nb_payment
 11      from payment_tbl,
 12           table(cast(multiset(select level from dual
 13                               connect by level <= number_of_payments)
 14                 as sys.odciNumberList))
 15    )
 16  select d.payment_id,
 17         sum(case when d.yr = d.minyr+0 then d.amount end) year1,
 18         sum(case when d.yr = d.minyr+1 then d.amount end) year2,
 19         sum(case when d.yr = d.minyr+2 then d.amount end) year3,
 20         sum(case when d.yr = d.minyr+3 then d.amount end) year4,
 21         sum(case when d.yr = d.minyr+4 then d.amount end) year5,
 22         sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
 23         sum(d.amount) total,
 24         max(case
 25               when    d.payment_id is null or p.payment_id is null
 26                    or p.nb_payment != d.nb_payment
 27                    or p.amount != d.amount
 28                 then 1/0 end) "Inconsistency in tables",
 29         max(case
 30               when d.number_of_payments != d.number_of_months
 31                 then 1/0 end) "Discrepancy in payment_tbl"
 32  from data d
 33         full outer join
 34       ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
 35         from payment_schedule_tbl
 36         group by payment_id, substr(period_name,5), amount ) p
 37         on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
 38  group by d.payment_id, d.minyr
 39  order by d.payment_id
               then 1/0 end) "Discrepancy in payment_tbl"
                     *
ERROR at line 31:
ORA-01476: divisor is equal to zero
The part between "" in the error message gives the origin of the error, ignore the rest.
Now deleting a row in "payment_schedule_tbl":
SQL> rollback;

Rollback complete.

SQL> delete payment_schedule_tbl where period_name='JAN-2025';

1 row deleted.

SQL> with
  2    data as (
  3      select payment_id, amount, number_of_payments,
  4             months_between(end_date+1,start_date) number_of_months,
  5             extract(year from add_months(start_date,column_value-1)) yr,
  6             min(extract(year from add_months(start_date,column_value-1)))
  7               over (partition by payment_id) minyr,
  8             count(*) over
  9               (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
 10               nb_payment
 11      from payment_tbl,
 12           table(cast(multiset(select level from dual
 13                               connect by level <= number_of_payments)
 14                 as sys.odciNumberList))
 15    )
 16  select d.payment_id,
 17         sum(case when d.yr = d.minyr+0 then d.amount end) year1,
 18         sum(case when d.yr = d.minyr+1 then d.amount end) year2,
 19         sum(case when d.yr = d.minyr+2 then d.amount end) year3,
 20         sum(case when d.yr = d.minyr+3 then d.amount end) year4,
 21         sum(case when d.yr = d.minyr+4 then d.amount end) year5,
 22         sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
 23         sum(d.amount) total,
 24         max(case
 25               when    d.payment_id is null or p.payment_id is null
 26                    or p.nb_payment != d.nb_payment
 27                    or p.amount != d.amount
 28                 then 1/0 end) "Inconsistency in tables",
 29         max(case
 30               when d.number_of_payments != d.number_of_months
 31                 then 1/0 end) "Discrepancy in payment_tbl"
 32  from data d
 33         full outer join
 34       ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
 35         from payment_schedule_tbl
 36         group by payment_id, substr(period_name,5), amount ) p
 37         on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
 38  group by d.payment_id, d.minyr
 39  order by d.payment_id
 40  /
               then 1/0 end) "Inconsistency in tables",
                     *
ERROR at line 28:
ORA-01476: divisor is equal to zero
or modifying an amount in this table:
SQL> rollback;

Rollback complete.

SQL> update payment_schedule_tbl set amount = 200 where period_name='JAN-2025';

1 row updated.

SQL> with
  2    data as (
  3      select payment_id, amount, number_of_payments,
  4             months_between(end_date+1,start_date) number_of_months,
  5             extract(year from add_months(start_date,column_value-1)) yr,
  6             min(extract(year from add_months(start_date,column_value-1)))
  7               over (partition by payment_id) minyr,
  8             count(*) over
  9               (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
 10               nb_payment
 11      from payment_tbl,
 12           table(cast(multiset(select level from dual
 13                               connect by level <= number_of_payments)
 14                 as sys.odciNumberList))
 15    )
 16  select d.payment_id,
 17         sum(case when d.yr = d.minyr+0 then d.amount end) year1,
 18         sum(case when d.yr = d.minyr+1 then d.amount end) year2,
 19         sum(case when d.yr = d.minyr+2 then d.amount end) year3,
 20         sum(case when d.yr = d.minyr+3 then d.amount end) year4,
 21         sum(case when d.yr = d.minyr+4 then d.amount end) year5,
 22         sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
 23         sum(d.amount) total,
 24         max(case
 25               when    d.payment_id is null or p.payment_id is null
 26                    or p.nb_payment != d.nb_payment
 27                    or p.amount != d.amount
 28                 then 1/0 end) "Inconsistency in tables",
 29         max(case
 30               when d.number_of_payments != d.number_of_months
 31                 then 1/0 end) "Discrepancy in payment_tbl"
 32  from data d
 33         full outer join
 34       ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
 35         from payment_schedule_tbl
 36         group by payment_id, substr(period_name,5), amount ) p
 37         on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
 38  group by d.payment_id, d.minyr
 39  order by d.payment_id
 40  /
               then 1/0 end) "Inconsistency in tables",
                     *
ERROR at line 28:
ORA-01476: divisor is equal to zero
Smile
Re: transpose rows into columns [message #685064 is a reply to message #685063] Fri, 15 October 2021 14:57 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thanks Michel for the solution and possible scenarios that will cause an error.
I will test the 1st Sql with different scenarios and let you know the test results.

And is there any possibility to get the start_date and end_date in the output along with the payment_id, Year1, Year2, Year3, Year4, Year5, Thereafter, Total columns.

Regards
Suji

[Updated on: Fri, 15 October 2021 14:59]

Report message to a moderator

Re: transpose rows into columns [message #685065 is a reply to message #685064] Sat, 16 October 2021 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With query on payment_schedule_tbl:
SQL> with
  2    data as (
  3      select payment_id, amount,
  4             to_number(substr(period_name,5)) yr,
  5             to_number(min(substr(period_name,5)) over (partition by payment_id)) minyr,
  6             min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
  7               over (partition by payment_id) start_date,
  8             add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
  9               over (partition by payment_id),1)-1 end_date
 10      from payment_schedule_tbl
 11    )
 12  select payment_id, start_date, end_date,
 13         sum(case when yr = minyr+0 then amount end) year1,
 14         sum(case when yr = minyr+1 then amount end) year2,
 15         sum(case when yr = minyr+2 then amount end) year3,
 16         sum(case when yr = minyr+3 then amount end) year4,
 17         sum(case when yr = minyr+4 then amount end) year5,
 18         sum(case when yr > minyr+4 then amount end) thereafter,
 19         sum(amount) total
 20  from data
 21  group by payment_id, minyr, start_date, end_date
 22  order by payment_id
 23  /
PAYMENT_ID START_DATE  END_DATE         YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100 01-AUG-2021 31-JUL-2026        500       1200       1200       1200       1200        700       6000

With query on payment_tbl:
SQL> with
  2    data as (
  3      select payment_id, amount,
  4             extract(year from add_months(start_date,column_value-1)) yr,
  5             min(extract(year from add_months(start_date,column_value-1)))
  6               over (partition by payment_id) minyr,
  7             min(start_date) over (partition by payment_id) start_date,
  8             max(end_date) over (partition by payment_id) end_date
  9      from payment_tbl,
 10           table(cast(multiset(select level from dual
 11                               connect by level <= number_of_payments)
 12                 as sys.odciNumberList))
 13    )
 14  select payment_id, start_date, end_date,
 15         sum(case when yr = minyr+0 then amount end) year1,
 16         sum(case when yr = minyr+1 then amount end) year2,
 17         sum(case when yr = minyr+2 then amount end) year3,
 18         sum(case when yr = minyr+3 then amount end) year4,
 19         sum(case when yr = minyr+4 then amount end) year5,
 20         sum(case when yr > minyr+4 then amount end) thereafter,
 21         sum(amount) total
 22  from data
 23  group by payment_id, minyr, start_date, end_date
 24  order by payment_id
 25  /
PAYMENT_ID START_DATE  END_DATE         YEAR1      YEAR2      YEAR3      YEAR4      YEAR5 THEREAFTER      TOTAL
---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     56100 01-AUG-2021 31-JUL-2026        500       1200       1200       1200       1200        700       6000

I updated my full query to cover the following discrepancies (I let you verify):
  • in "payment_tbl", "number_of_payments" does match with months between start_date and end_date
  • in "payment_schedule_tbl", some months contain more than one row
  • numbers of months in the 2 tables don't match
  • amounts are not the same in both tables
  • start dates or end dates in the 2 table don't match
col "Discrepancy in payment_tbl" noprint
col "Inconsistency in tables"    noprint
Col "Duplicated month"           noprint
with
  data as (
    select payment_id, amount, number_of_payments, 
           months_between(end_date+1,start_date) number_of_months,
           extract(year from add_months(start_date,column_value-1)) yr,
           min(extract(year from add_months(start_date,column_value-1)))
             over (partition by payment_id) minyr,
           min(start_date) over (partition by payment_id) start_date,
           max(end_date) over (partition by payment_id) end_date,
           count(*) over 
             (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
             nb_payment
    from payment_tbl,
         table(cast(multiset(select level from dual
                             connect by level <= number_of_payments)
               as sys.odciNumberList))
  )
select d.payment_id, d.start_date, d.end_date,
       sum(case when d.yr = d.minyr+0 then d.amount end) year1,
       sum(case when d.yr = d.minyr+1 then d.amount end) year2,
       sum(case when d.yr = d.minyr+2 then d.amount end) year3,
       sum(case when d.yr = d.minyr+3 then d.amount end) year4,
       sum(case when d.yr = d.minyr+4 then d.amount end) year5,
       sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
       sum(d.amount) total,
       max(case 
             when    d.payment_id is null or p.payment_id is null 
                  or p.nb_payment != d.nb_payment
                  or p.amount != d.amount
                  or p.start_date != d.start_date
                  or p.end_date != d.end_date
               then 1/0 end) "Inconsistency in tables",
       max(case
             when p.maxcnt > 1
               then 1/0 end) "Duplicated month",
       max(case 
             when d.number_of_payments != d.number_of_months 
               then 1/0 end) "Discrepancy in payment_tbl"
from data d 
       full outer join 
     ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount,
              start_date, end_date, maxcnt
       from ( select p.*, max(cnt) over (partition by payment_id) maxcnt
               from ( select p.*, 
                              min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')) 
                                over (partition by payment_id) start_date,
                              add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')) 
                                over (partition by payment_id),1)-1 end_date,
                              count(*) over (partition by payment_id, period_name) cnt
                      from payment_schedule_tbl p ) p ) p
       group by payment_id, substr(period_name,5), amount, start_date, end_date, maxcnt ) p
       on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
group by d.payment_id, d.minyr, d.start_date, d.end_date
order by d.payment_id
/

[Updated on: Sat, 16 October 2021 15:10]

Report message to a moderator

Re: transpose rows into columns [message #685068 is a reply to message #685065] Sun, 17 October 2021 11:14 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Thanks for sharing the modified SQL.

Please help me with the new SQL that displays new output columns or fields in addition to the existing output columns.
Those new columns are from the other table(s).

CREATE TABLE PAYMENT_HDR_TBL
(PAYMENT_ID INT NOT NULL, PAYMENT_NUMBER VARCHAR(20), DESCR VARCHAR(50), ASSET_NUMBER VARCHAR(15), PAYMENT_TYPE VARCHAR(10))
INSERT INTO PAYMENT_HDR_TBL VALUES (56100, 'VH2678190', 'VH-DESCR-2678190', '56719201', 'MONTHLY');


PAYMENT_NUMBER  DESCR               ASSET_NUMBER  PAYMENT_TYPE  PAYMENT_ID   START_DATE  END_DATE         YEAR1      YEAR2      
--------------- -----------------   -----------   -----------   ----------    ---------- ----------      ---------- ---------- 
VH2678190       VH-DESCR-2678190    56719201      MONTHLY         56100      01-AUG-2021  31-JUL-2026        500       1200     

YEAR3       YEAR4       YEAR5   THEREAFTER       TOTAL
---------- ----------  ------  -----------     -----------
  1200       1200      1200        700              6000
I tried modifying the logic but results are not as expected. hence requesting you please help me with the new sql.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #685071 is a reply to message #685068] Sun, 17 October 2021 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It's quite simple, I let you find it.
Here's a snippet from any of my queries:
with
 data as (...),
 prevq as (<previous main query>)
select ...
from prevq, newtab
where prevq.payment_id = newtab.payment_id
order by payment_id
/
Re: transpose rows into columns [message #685103 is a reply to message #685071] Sat, 23 October 2021 12:04 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

I completed the query with the help of snippt that you provided me. It’s working perfectly and results are as expected. Thank you so much for your help in this regard.

Now I have couple of questions for you on this query it self.
1. Results are appearing wrong when adding new column to the report.
I tried different ways like using union logic or sub query logic to get the extra column in the report.
Only one column causing the issue hence without disturbing this working query I would like to get this new field into report.
Can you please help me with code snippt.
2. Year1, year2, year3, year4, year5, thereafter logic should be derived using input year value entered. Example: year1 should be 2020 and do on… if the entered input year value is 2020 though start date in payment_schedule_tbl is 2019.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #685106 is a reply to message #685103] Sat, 23 October 2021 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. If your added columns come from other tables than those used in the first queries and payment_id is a PK for these tables then you just have to do the same thing than in your previous post. If not you have to specify more deeply your input.

2. In the above queries, the report start with year determine by "minyr" which is computed in the "data" subquery. If you want to start from another year then just change "d.minyr" by the year you want. If this year is greater then the minyr and if you don't want the total contains amounts before the specified year then you have to change "sum(amount)" to restrict it to only the years you want (using "case" for instance).

Post the query/ies you find in the end to help future readers.

Re: transpose rows into columns [message #685128 is a reply to message #685106] Wed, 27 October 2021 12:09 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Thanks for the response. I completed with 1st point and sql works fine. However I am not clear with 2nd point.
Could you please help me with the modified sql logic for the 2nd point.

Thank you.

Regards
Suji
Re: transpose rows into columns [message #685129 is a reply to message #685128] Wed, 27 October 2021 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> def start_year=2021
SQL> with
  2    data as (
  3      select payment_id, amount,
  4             to_number(substr(period_name,5)) yr,
  5             min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
  6               over (partition by payment_id) start_date,
  7             add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
  8               over (partition by payment_id),1)-1 end_date
  9      from payment_schedule_tbl
 10    )
 11  select payment_id, start_date, end_date,
 12         nvl(sum(case when yr = &start_year+0 then amount end),0) year1,
 13         nvl(sum(case when yr = &start_year+1 then amount end),0) year2,
 14         nvl(sum(case when yr = &start_year+2 then amount end),0) year3,
 15         nvl(sum(case when yr = &start_year+3 then amount end),0) year4,
 16         nvl(sum(case when yr = &start_year+4 then amount end),0) year5,
 17         nvl(sum(case when yr > &start_year+4 then amount end),0) thereafter,
 18         nvl(sum(case when yr < &start_year then amount end),0) total_before_&start_year,
 19         nvl(sum(case when yr >= &start_year then amount end),0) total_from_&start_year,
 20         sum(amount) overall_total
 21  from data
 22  group by payment_id, start_date, end_date
 23  order by payment_id
 24  /
PAYMENT_ID START_DATE  END_DATE    YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2021 TOTAL_FROM_2021 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
     56100 01-AUG-2021 31-JUL-2026   500  1200  1200  1200  1200        700                 0            6000          6000

1 row selected.

SQL> def start_year=2023
SQL> /
PAYMENT_ID START_DATE  END_DATE    YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2023 TOTAL_FROM_2023 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
     56100 01-AUG-2021 31-JUL-2026  1200  1200  1200   700     0          0              1700            4300          6000

1 row selected.

SQL> def start_year=2019
SQL> /
PAYMENT_ID START_DATE  END_DATE    YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2019 TOTAL_FROM_2019 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
     56100 01-AUG-2021 31-JUL-2026     0     0   500  1200  1200       3100                 0            6000          6000

1 row selected.
Re: transpose rows into columns [message #685130 is a reply to message #685129] Wed, 27 October 2021 12:46 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Thanks for your prompt response and modified sql.
I will be testing the scenarios with this new sql.

Regards
Suji
Re: transpose rows into columns [message #685145 is a reply to message #685130] Thu, 28 October 2021 22:03 Go to previous message
Sekhar6617
Messages: 22
Registered: March 2021
Junior Member
Hi Michel,

Latest sql query with date parameter as input is working perfectly and results are as expected.
Thank you so much for all your efforts on this sql.

Regards
Suji
Previous Topic: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle?
Next Topic: Unable to take backup of a table
Goto Forum:
  


Current Time: Thu Mar 28 03:43:37 CDT 2024