We have a database table TBLTEST. Below is part of its data:

CODE1	CODE2	CODE3	RATE	VALUE	MONTH
A	B	C	1	1	202001
A	B	C	1	1	202002
A	B	C	1	1	202003
A	B	C	2	1	202004
A	B	C	2	1	202005
A	B	C	1	1	202006
A	B	C	1	1	202007
A	B	C	1	1	202008
A	B	C	1	1	202009

The data is ordered by date. We are trying to group rows by the first five columns, convert dates in each group into an interval, and record the ending date of the last record as the infinite date 99991230. Below is the desired result:

CODE1	CODE2	CODE3	RATE	VALUE	STARTDT	ENDDT
A	B	C	1	1	20200101	20200331
A	B	C	2	1	20200401	20200531
A	B	C	1	1	20200601	99991230

Oracle SQL:

SELECT

     code1,

     code2,

     code3,

     rate,

     value,

     min(MONTH) start_dt,

     CASE

          WHEN ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BYmax(MONTH) DESC) = 1 THEN 99991230

          ELSE max(MONTH)

     END end_dt

FROM

     (

     SELECT

          t.*,

          ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BY MONTH) rn1,

          ROW_NUMBER() OVER(PARTITION BY code1, code2, code3, rate, value ORDERBY MONTH) rn2

     FROM

          TBLTEST t

) t

GROUP BY

     code1,

     code2,

     code3,

     rate,

     value,

     rn1 - rn2

ORDER BY

     start_dt

It is rather simple to perform the task in the natural way of thinking. We compare neighboring values between rows on the first five columns, and put the current one and the previous row in the same group when values are same, or create a new group if they are different until the last record is compared. As SQL set is unordered, we need to first invent two columns of indexes manually in an extremely complicated way and then perform grouping according to the relationship between the two columns of indexes. You need to be really smart to come up with the solution.

Yet it is easy to write the code using the open-source esProc SPL:

	A
1	=connect("oracle")
2	=A1.query@x("SELECT * FROM TBLTEST ORDER BY MONTH")
3	=A2.groups@o(CODE1,CODE2,CODE3,RATE,VALUE;min(MONTH)/"01":STARTDT,string(date((max(MONTH)+1)/"01","yyyyMMdd")-1,"yyyyMMdd"):ENDDT)
4	>A3.m(-1).modify("99991230":ENDDT)

 

SPL supports ordered sets directly, making it easy to perform grouping when a neighboring value is different.

 

Q & A Collection

https://stackoverflow.com/questions/64099063/oracle-sql-data-migration-row-to-column-based-in-month