Converter linhas em colunas SQL

SQL Server

SQL

Banco de Dados

24/12/2023

Estou com o seguinte comando:

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

Mylena

Curtidas 0

Respostas

P3rm4tr1p

P3rm4tr1p

24/12/2023

Estou com o seguinte comando:

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.
GOSTEI 0
Mylena

Mylena

24/12/2023

como vou somar vários valores para essas colunas ?

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 ?
GOSTEI 0
Arthur Heinrich

Arthur Heinrich

24/12/2023

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:

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

GOSTEI 0
POSTAR