Pivot Room Sqlite

Question

Good night, you guys.

I am using select below, but I would like the turns to be columns. As there is no pivot option, how can I do this?

select tma.turma as TURMAS, tno.turno as TURNOS, count (tno.turno) as QTDE
from (select * from Visita v 
where 
(v.dataCadastro BETWEEN (strftime(v.dataCadastro, '2021-04-01')) AND 
(strftime(v.dataCadastro, '2021-05-31')))) resultado
INNER JOIN Turma tma ON (resultado.turma_id = tma.id) 
INNER JOIN Turno tno ON (resultado.turno_id = tno.id) 
group by TURMAS, TURNOS

For example:

I saw some solutions with CASE, but there may be other registrations of "classes" and "shifts" later, so it would not help me.

Answer 1

not always the pivot will be a viable option, but there are alternatives to it

Voce could try using CASE WHEN

with PIVOT

SELECT U.ANO, U.BANCO, U.INVESTIMENTOS, U.OUTROS
FROM @CONTAS AS C
PIVOT (
    SUM(C.VALOR) FOR
    C.TIPO IN (INVESTIMENTOS, OUTROS)  
) AS U

with CASE WHEN da to do the same

SELECT C.ANO, C.BANCO, 
    INVESTIMENTOS = SUM(CASE WHEN C.TIPO = 'INVESTIMENTOS' THEN C.VALOR END),
    OUTROS = SUM(CASE WHEN C.TIPO = 'OUTROS' THEN C.VALOR END)
FROM @CONTAS AS C
GROUP BY C.ANO, C.BANCO

good luck!