Creating a quarterly report in SQL

Question

I'm creating a quarterly report of item usage in Firebird, and got stuck at this point:

The select below is working, but it brings me the monthly usage of last 3 months like the image below:

Script:

SELECT
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND L.ANO = 2023) GROUP BY L.ITEM) AS CONSUMO1,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO2,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO3,
C.MES,
C.ANO,
L.ITEM,
I.NOME,
I.UNI_CON,
I.CONVER,
I.UNI_COMP,
MAX(I.EST_MAX) EST_MAXIMO,
MAX(I.CUSTO) PRECO,
MAX(EST_MIN) EST_MINIMO,
MAX(I.ESTOQUE) ESTOQUE,
SUM(L.QTDE) QUANTIDADE
FROM GECADSAI C INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
    WHERE   (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
        AND C.CDC NOT BETWEEN 9901 AND 9999
        AND I.REF = 1
        AND L.CONSOL = 'T'
        AND C.CONSOL = 'T'
             GROUP BY L.ITEM, I.NOME, C.ANO, C.MES, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

Result:

But, as you can see, the QUANTIDADE column split monthly line by line the total usage.

ITEM_NAME	MONTH	USAGE
ITEM A	JAN	7000
ITEM A	DEZ	3000
ITEM A	NOV	4000
ITEM B	JAN	200
ITEM B	DEZ	350
ITEM B	NOV	500

And I'd like to, each month of consumption to be a column of my select, so, it will be something like this:

ITEM_NAME	JAN	DEZ	NOV
ITEM A	7000	3000	4000
ITEM B	200	350	500

I also tried the commented subselect, but it will for sure returns me, multiple rows error.

SELECT
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND L.ANO = 2023) GROUP BY L.ITEM) AS CONSUMO1,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO2,
--(SELECT SUM(L.QTDE) FROM GELANSAI L WHERE L.CONSOL = 'T' AND (L.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND L.ANO = 2022) GROUP BY L.ITEM) AS CONSUMO3,
C.MES,
C.ANO,
L.ITEM,
I.NOME,
I.UNI_CON,
I.CONVER,
I.UNI_COMP,
MAX(I.EST_MAX) EST_MAXIMO,
MAX(I.CUSTO) PRECO,
MAX(EST_MIN) EST_MINIMO,
MAX(I.ESTOQUE) ESTOQUE,
SUM(L.QTDE) QUANTIDADE
FROM GECADSAI C INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
WHERE   (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
AND C.CDC NOT BETWEEN 9901 AND 9999
AND I.REF = 1
AND L.CONSOL = 'T'
AND C.CONSOL = 'T'
GROUP BY L.ITEM, I.NOME, C.ANO, C.MES, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

Answer 1

It is not possible to create a pivot with dynamic column names that depend on a date. However you can pivot values. Under the assumption that your first query produces the correct results, but just not the correct layout, you can do something like this:

SELECT
  L.ITEM,
  I.NOME,
  I.UNI_CON,
  I.CONVER,
  I.UNI_COMP,
  MAX(I.EST_MAX) EST_MAXIMO,
  MAX(I.CUSTO) PRECO,
  MAX(EST_MIN) EST_MINIMO,
  MAX(I.ESTOQUE) ESTOQUE,
  -- first month (3 months ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then C.MES end) M1, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then L.QTDE end) QUANTIDADE_M1
  -- second month (2 months ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then C.MES end) M2, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)) AND C.ANO = 2022 then L.QTDE end) QUANTIDADE_M1
  -- third month (1 month ago)
  max(case when C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) AND C.ANO = 2023 then C.MES end) M3, 
  sum(case when C.MES = EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE)) AND C.ANO = 2023 then L.QTDE end) QUANTIDADE_M3
FROM GECADSAI C 
INNER JOIN GELANSAI L ON C.ANO = L.ANO AND C.MES = L.MES AND C.DOC = L.DOC
LEFT JOIN GEITENS I ON L.ITEM = I.COD
WHERE (((C.MES = (EXTRACT(MONTH FROM DATEADD(-1 MONTH TO CURRENT_DATE))) AND C.ANO = 2023 ))
    OR ((C.MES = (EXTRACT(MONTH FROM DATEADD(-2 MONTH TO CURRENT_DATE)))) AND C.ANO = 2022)
    OR (C.MES = (EXTRACT(MONTH FROM DATEADD(-3 MONTH TO CURRENT_DATE))) AND C.ANO = 2022))
AND C.CDC NOT BETWEEN 9901 AND 9999
AND I.REF = 1
AND L.CONSOL = 'T'
AND C.CONSOL = 'T'
GROUP BY L.ITEM, I.NOME, I.UNI_CON, I.VLRMED, I.UNI_COMP, I.CONVER

That is, you sum the values for 3 months ago, 2 months ago and 1 month ago, and add a column that identifies the month (which must also use an aggregate column) as you can't generate the column name dynamically. The alternative is to generate columns for all twelve months.

The C.MES and C.ANO columns are no longer part of the GROUP BY.