Doubt column concatenation

Question

The code below returns a sum per classification. Something like this: Services = type 1 Materials = type 2

the expected result is that there is only one row, and that in this Row I have the value of each type per column .

select distinct
C.COD,
c.paciente,
--C.CON || '-' || T.NOME Convenio ,
C.TIPO_CTA,
l.tipo,
sum (L.VALOR_C) over(partition by (L.TIPO) ) total
from FCCTAEXT C
LEFT join FCLANEXT L on C.ID = L.ID_FCCTAEXT
left join TBCONVEN T on (T.COD = C.CON)
left join RECADATE E on E.REG = C.REG_ATE
where
c.reg_ate = 1654385 and
C.IND_EMI = 'T'
  and ((C.ANOPRO = 2021
  and C.MESPRO = 7))
  and C.TIPO_CTA = 'E'
  and C.CON = 12

Is there any alternative to subselect to handle this case ?

Answer 1

Firebird does not support pivot table, but if you need this on-screen data, from to use the most up-to-date cxGrid of the devExpress palette, it supports pivot table ok

another way out would be to process the data returned from your select into something in memory, such as a Clientdataset for example, locating existing records and adding values to them, and only in the end exporting this data to where you need it