Fórum Converter linhas em colunas SQL #620883
24/12/2023
0
Estou com o seguinte comando:
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 |
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
Curtir tópico
+ 0
Responder
Posts
25/12/2023
P3rm4tr1p
Estou com o seguinte comando:
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 |
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 pvtPara 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.
Responder
Gostei + 0
26/12/2023
Mylena
como vou somar vários valores para essas colunas ?
exemplo: consegui fazer o pivot da somatória do faturamento:
Agora como vou somar nessas mesmas colunas, o total do mês do CPV, Margem bruta, toneladas e ticket médio ?
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 ?
Responder
Gostei + 0
26/12/2023
Arthur Heinrich
Sua query é bastante particular. Acho que nenhuma funcionalidade nativa vai te entregar o que você está querendo.
Precisa fazer algum tipo de "gambiarra" para isso:
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
Responder
Gostei + 0
Clique aqui para fazer login e interagir na Comunidade :)