Converter linhas em colunas SQL
24/12/2023
0
Select YEAR (T.DATA) ANO, CHOOSE (month (t.data), 'JAN', 'FEV', 'MAR', 'ABR','MAI', 'JUN', 'JUL', 'AGO', 'SET', 'OUT', 'NOV', 'DEZ') AS MES, sum (o.VLRAFATURAR) 'FATURAMENTO', sum (ROUND (s.CUSTOMEDIO * o.QTDADE,2 )) 'CPV', sum (ROUND (o.VLRAFATURAR - s.CUSTOMEDIO,2)) '% MARGEM BRUTA', sum (ROUND ((o.QTDADE * u.EQUIVALENCIA)/1000,2)) 'TONELADAS', sum (ROUND (O.VLRAFATURAR/((o.QTDADE * u.EQUIVALENCIA)/100),2)) 'TICKET MEDIO' From TRANSACAO T inner join ORDTRANS O on T.CODTRANSACAO=O.CODTRANSACAO inner join ORDSALDO S on s.CODORDTRANS=o.CODORDTRANS inner join PRODUTO P on o.codprod=p.CODPROD inner join UNIDADE U on u.CODUNID=p.CODUNID where t.DATA between '01/01/2023' and '30/12/2023' AND t.SITUACAO=3 AND o.CODNATU in ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P') GROUP BY Year(t.DATA), MONTH (t.DATA) ORDER BY MONTH (t.DATA)
Como transformo as linhas ANO e MES para colunas ?
E como na ultima coluna o total de todo faturamento por exemplo, de todos os meses ?
Exemplo o resultado esperado:
Ano 2023..........| Jan | Fev | Mar | Abr | Mai....... | Total
Faturamento |
CPV |
Margem bruta |
Toneladas |
Ticket Médio |
Mylena
Posts
25/12/2023
P3rm4tr1p
Select YEAR (T.DATA) ANO, CHOOSE (month (t.data), 'JAN', 'FEV', 'MAR', 'ABR','MAI', 'JUN', 'JUL', 'AGO', 'SET', 'OUT', 'NOV', 'DEZ') AS MES, sum (o.VLRAFATURAR) 'FATURAMENTO', sum (ROUND (s.CUSTOMEDIO * o.QTDADE,2 )) 'CPV', sum (ROUND (o.VLRAFATURAR - s.CUSTOMEDIO,2)) '% MARGEM BRUTA', sum (ROUND ((o.QTDADE * u.EQUIVALENCIA)/1000,2)) 'TONELADAS', sum (ROUND (O.VLRAFATURAR/((o.QTDADE * u.EQUIVALENCIA)/100),2)) 'TICKET MEDIO' From TRANSACAO T inner join ORDTRANS O on T.CODTRANSACAO=O.CODTRANSACAO inner join ORDSALDO S on s.CODORDTRANS=o.CODORDTRANS inner join PRODUTO P on o.codprod=p.CODPROD inner join UNIDADE U on u.CODUNID=p.CODUNID where t.DATA between '01/01/2023' and '30/12/2023' AND t.SITUACAO=3 AND o.CODNATU in ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P') GROUP BY Year(t.DATA), MONTH (t.DATA) ORDER BY MONTH (t.DATA)
Como transformo as linhas ANO e MES para colunas ?
E como na ultima coluna o total de todo faturamento por exemplo, de todos os meses ?
Exemplo o resultado esperado:
Ano 2023..........| Jan | Fev | Mar | Abr | Mai....... | Total
Faturamento |
CPV |
Margem bruta |
Toneladas |
Ticket Médio |
Para converter as linhas ANO e MES para colunas, você pode usar o operador PIVOT. A sintaxe básica é a seguinte:
SELECT colunas_desejadas, [valor_a_pivotar] FROM tabela PIVOT ( SUM([valor_a_pivotar]) FOR [coluna_a_pivotar] IN ([valores_da_coluna_a_pivotar]) ) AS pvt
No seu caso, você deseja pivotar as colunas ANO e MES. Portanto, a sintaxe ficaria assim:
SELECT ANO, MES, sum(FATURAMENTO) AS FATURAMENTO, sum(CPV) AS CPV, sum(MARGEM_BRUTA) AS MARGEM_BRUTA, sum(TONELADAS) AS TONELADAS, sum(TICKET_MEDIO) AS TICKET_MEDIO FROM TRANSACAO T INNER JOIN ORDTRANS O ON T.CODTRANSACAO = O.CODTRANSACAO INNER JOIN ORDSALDO S ON S.CODORDTRANS = O.CODORDTRANS INNER JOIN PRODUTO P ON O.CODPROD = P.CODPROD INNER JOIN UNIDADE U ON U.CODUNID = P.CODUNID WHERE T.DATA BETWEEN '01/01/2023' AND '30/12/2023' AND T.SITUACAO = 3 AND O.CODNATU IN ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P') GROUP BY YEAR(T.DATA), MONTH(T.DATA) PIVOT ( SUM([valor_a_pivotar]) FOR [coluna_a_pivotar] IN ('ANO', 'MES') ) AS pvt
Para converter as linhas ANO e MES para colunas, você pode usar o operador PIVOT. A sintaxe básica é a seguinte:
SQL
SELECT
colunas_desejadas,
[valor_a_pivotar]
FROM tabela
PIVOT (
SUM([valor_a_pivotar])
FOR [coluna_a_pivotar] IN ([valores_da_coluna_a_pivotar])
) AS pvt
Use o código com cuidado. Saiba mais
No seu caso, você deseja pivotar as colunas ANO e MES. Portanto, a sintaxe ficaria assim:
SQL
SELECT
ANO,
MES,
sum(FATURAMENTO) AS FATURAMENTO,
sum(CPV) AS CPV,
sum(MARGEM_BRUTA) AS MARGEM_BRUTA,
sum(TONELADAS) AS TONELADAS,
sum(TICKET_MEDIO) AS TICKET_MEDIO
FROM
TRANSACAO T
INNER JOIN ORDTRANS O ON T.CODTRANSACAO = O.CODTRANSACAO
INNER JOIN ORDSALDO S ON S.CODORDTRANS = O.CODORDTRANS
INNER JOIN PRODUTO P ON O.CODPROD = P.CODPROD
INNER JOIN UNIDADE U ON U.CODUNID = P.CODUNID
WHERE
T.DATA BETWEEN '01/01/2023' AND '30/12/2023'
AND T.SITUACAO = 3
AND O.CODNATU IN ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P')
GROUP BY
YEAR(T.DATA),
MONTH(T.DATA)
PIVOT (
SUM([valor_a_pivotar])
FOR [coluna_a_pivotar] IN ('ANO', 'MES')
) AS pvt
Use o código com cuidado. Saiba mais
Com essa consulta, você obterá o seguinte resultado:
Para obter o total de faturamento em uma única coluna, você pode usar a função SUM. A sintaxe seria a seguinte:
SELECT ANO, MES, sum(FATURAMENTO) AS FATURAMENTO, sum(CPV) AS CPV, sum(MARGEM_BRUTA) AS MARGEM_BRUTA, sum(TONELADAS) AS TONELADAS, sum(TICKET_MEDIO) AS TICKET_MEDIO, sum(FATURAMENTO) OVER (PARTITION BY ANO) AS TOTAL_FATURAMENTO FROM TRANSACAO T INNER JOIN ORDTRANS O ON T.CODTRANSACAO = O.CODTRANSACAO INNER JOIN ORDSALDO S ON S.CODORDTRANS = O.CODORDTRANS INNER JOIN PRODUTO P ON O.CODPROD = P.CODPROD INNER JOIN UNIDADE U ON U.CODUNID = P.CODUNID WHERE T.DATA BETWEEN '01/01/2023' AND '30/12/2023' AND T.SITUACAO = 3 AND O.CODNATU IN ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922',
É isso, até mais.
26/12/2023
Mylena
exemplo: consegui fazer o pivot da somatória do faturamento:
select * from ( Select --YEAR (T.DATA) ANO, CHOOSE (month (t.data), 'JAN', 'FEV', 'MAR', 'ABR','MAI', 'JUN', 'JUL', 'AGO', 'SET', 'OUT', 'NOV', 'DEZ') AS MES, sum (o.VLRAFATURAR) 'FATURAMENTO', sum (ROUND (s.CUSTOMEDIO * o.QTDADE,2 )) 'CPV', sum (ROUND (o.VLRAFATURAR - s.CUSTOMEDIO,2)) '% MARGEM BRUTA', sum (ROUND ((o.QTDADE * u.EQUIVALENCIA)/1000,2)) 'TONELADAS', sum (ROUND (O.VLRAFATURAR/((o.QTDADE * u.EQUIVALENCIA)/100),2)) 'TICKET MEDIO' --sum(o.vlrafaturar) OVER (PARTITION BY ANO) AS TOTAL_FATURAMENTO From TRANSACAO T inner join ORDTRANS O on T.CODTRANSACAO=O.CODTRANSACAO inner join ORDSALDO S on s.CODORDTRANS=o.CODORDTRANS inner join PRODUTO P on o.codprod=p.CODPROD inner join UNIDADE U on u.CODUNID=p.CODUNID where t.DATA between '01/01/2023' and '30/12/2023' AND t.SITUACAO=3 AND o.CODNATU in ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P') GROUP BY grouping sets (Year(t.DATA), MONTH (t.DATA)) )DADOS_PIVOT PIVOT ( sum(faturamento) for mes in ([JAN], [FEV], [MAR], [ABR],[MAI], [JUN], [JUL], [AGO], [SET], [OUT], [NOV], [DEZ])) RESULT_PIVOT
Agora como vou somar nessas mesmas colunas, o total do mês do CPV, Margem bruta, toneladas e ticket médio ?
26/12/2023
Arthur Heinrich
Precisa fazer algum tipo de "gambiarra" para isso:
with dados as (select year(t.data) ano, month(t.data) mes, sum (o.VLRAFATURAR) fat, sum (ROUND (s.CUSTOMEDIO * o.QTDADE,2 )) cpv, sum (ROUND (o.VLRAFATURAR - s.CUSTOMEDIO,2)) mb, sum (ROUND ((o.QTDADE * u.EQUIVALENCIA)/1000,2)) t, sum (ROUND (O.VLRAFATURAR/((o.QTDADE * u.EQUIVALENCIA)/100),2)) tm from TRANSACAO T inner join ORDTRANS O on T.CODTRANSACAO=O.CODTRANSACAO inner join ORDSALDO S on s.CODORDTRANS=o.CODORDTRANS inner join PRODUTO P on o.codprod=p.CODPROD inner join UNIDADE U on u.CODUNID=p.CODUNID where t.DATA between '01/01/2023' and '30/12/2023' AND t.SITUACAO=3 AND o.CODNATU in ('5101', '5101A', '5101C', '5101P', '5102', '5102CF', '5102P','5116', '5124','5922', '6101P') GROUP BY year(t.data), month(t.data)), dados2 as (select ano, 'FATURAMENTO' valor, sum(case when mes = 1 then fat else 0 end) jan, sum(case when mes = 2 then fat else 0 end) fev, sum(case when mes = 3 then fat else 0 end) mar, sum(case when mes = 4 then fat else 0 end) abr, sum(case when mes = 5 then fat else 0 end) mai, sum(case when mes = 6 then fat else 0 end) jun, sum(case when mes = 7 then fat else 0 end) jul, sum(case when mes = 8 then fat else 0 end) ago, sum(case when mes = 9 then fat else 0 end) [set], sum(case when mes = 10 then fat else 0 end) out, sum(case when mes = 11 then fat else 0 end) nov, sum(case when mes = 12 then fat else 0 end) dez from dados group by ano union all select ano, 'CPV' valor, sum(case when mes = 1 then cpv else 0 end) jan, sum(case when mes = 2 then cpv else 0 end) fev, sum(case when mes = 3 then cpv else 0 end) mar, sum(case when mes = 4 then cpv else 0 end) abr, sum(case when mes = 5 then cpv else 0 end) mai, sum(case when mes = 6 then cpv else 0 end) jun, sum(case when mes = 7 then cpv else 0 end) jul, sum(case when mes = 8 then cpv else 0 end) ago, sum(case when mes = 9 then cpv else 0 end) [set], sum(case when mes = 10 then cpv else 0 end) out, sum(case when mes = 11 then cpv else 0 end) nov, sum(case when mes = 12 then cpv else 0 end) dez from dados group by ano union all select ano, '% MARGEM BRUTA' valor, sum(case when mes = 1 then mb else 0 end) jan, sum(case when mes = 2 then mb else 0 end) fev, sum(case when mes = 3 then mb else 0 end) mar, sum(case when mes = 4 then mb else 0 end) abr, sum(case when mes = 5 then mb else 0 end) mai, sum(case when mes = 6 then mb else 0 end) jun, sum(case when mes = 7 then mb else 0 end) jul, sum(case when mes = 8 then mb else 0 end) ago, sum(case when mes = 9 then mb else 0 end) [set], sum(case when mes = 10 then mb else 0 end) out, sum(case when mes = 11 then mb else 0 end) nov, sum(case when mes = 12 then mb else 0 end) dez from dados group by ano union all select ano, 'TONELADAS' valor, sum(case when mes = 1 then t else 0 end) jan, sum(case when mes = 2 then t else 0 end) fev, sum(case when mes = 3 then t else 0 end) mar, sum(case when mes = 4 then t else 0 end) abr, sum(case when mes = 5 then t else 0 end) mai, sum(case when mes = 6 then t else 0 end) jun, sum(case when mes = 7 then t else 0 end) jul, sum(case when mes = 8 then t else 0 end) ago, sum(case when mes = 9 then t else 0 end) [set], sum(case when mes = 10 then t else 0 end) out, sum(case when mes = 11 then t else 0 end) nov, sum(case when mes = 12 then t else 0 end) dez from dados group by ano union all select ano, 'TICKET MEDIO' valor, sum(case when mes = 1 then tm else 0 end) jan, sum(case when mes = 2 then tm else 0 end) fev, sum(case when mes = 3 then tm else 0 end) mar, sum(case when mes = 4 then tm else 0 end) abr, sum(case when mes = 5 then tm else 0 end) mai, sum(case when mes = 6 then tm else 0 end) jun, sum(case when mes = 7 then tm else 0 end) jul, sum(case when mes = 8 then tm else 0 end) ago, sum(case when mes = 9 then tm else 0 end) [set], sum(case when mes = 10 then tm else 0 end) out, sum(case when mes = 11 then tm else 0 end) nov, sum(case when mes = 12 then tm else 0 end) dez from dados group by ano) select ano, valor, jan, fev, mar, abr, mai, jun, jul, ago, [set], out, nov, dez from dados2 order by ano, case when valor = 'FATURAMENTO' then 1 when valor = 'CPV' then 2 when valor = '% MARGEM BRUTA' then 3 when valor = 'TONELADAS' then 4 when valor = 'TICKET MEDIO' then 5 end
Clique aqui para fazer login e interagir na Comunidade :)