Fórum Converter linhas em colunas SQL #620883

24/12/2023

0

SQL Server SQL Banco de Dados

Estou com o seguinte comando:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

Responder

Posts

25/12/2023

P3rm4tr1p

Estou com o seguinte comando:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 |

Ler Mais...





Para converter as linhas ANO e MES para colunas, você pode usar o operador PIVOT. A sintaxe básica é a seguinte:

1
2
3
4
5
6
7
8
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:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar