I have a table that as columns come: Department_Id, Year, January Amount, February_Amount, March_Amount, April_Amount, May_Amount….

The month columns store the amounts collected month by month from different years.

From parameters I receive the range of months to consult. For example: Initial_month, Final_month, Department_Id, year (1, 3, 4, 2023).

I should group the range of months in a column since they are interested in seeing the sum of months and not the month-by-month breakdown.

I am working on all of this with Oracle 19, I was considering creating a function to not make the query so long.

Any ideas on how to solve this issue, any help is greatly appreciated.

Thank you very much community.

Greetings

TRY THIS QUERY USING UNPIVOT AND COMBINING WITH A FUNCTION THAT WILL RETURN THE LIST OF THE MONTHS IN A CHAIN ​​TO ADD THEM TO THE IN INSIDE THE WHERE

SELECT ANIO, QQ, MESES
FROM BASE_MENSUAL
UNPIVOT (
  MESES FOR qq IN (MONTO_ENERO AS '1', MONTO_FEBRERO AS '2', MONTO_MARZO )
)
WHERE Department_Id = 4

AND QQ IN (F_MI_MF_PPTO(1, 3)) ---FUNCTION

--GROUP BY ANIO, QQ, MESES
;

IN THE FUNCTION I WAS CONSIDERING DOING ALL THE VALIDATIONS TO CONSIDER ALL MONTHS AS NUMBERS USING THE ALIAS COMBINED WITH UNPIVOT

create or replace FUNCTION           F_MI_MF_PPTO 
(
  MI IN NUMBER 
, MF IN NUMBER 
--, MESES OUT VARCHAR2
) RETURN VARCHAR2 AS 

MESES VARCHAR2(200);

BEGIN
IF(MI = 1 AND MF >=1)
THEN
 MESES := TO_CHAR(1);
 ELSIF (MI <=2 AND MF >=2)
 --(MESES:= MESES ||TO_CHAR(','));
 (MESES:= MESES || TO_CHAR(2));
 
END IF;

  RETURN MESES;
END F_MI_MF_PPTO;

WHEN ADDING SEPARATOR CHARACTER (,) MARKS ERROR IN THE FUNCTION

IT IS NOT MANDATORY FOR IT TO STAY THIS WAY, IT MAY BE UNNECESSARILY COMPLICATING FOR ME.

thank you