PIVOT - Ordenar as colunas

Question

Boa tarde,

Tenho o PIVOT  abaixo e quero ordenar as colunas que representam os meses em ordem crescente, mas não consegui, como fazer ?

declare @colunas_pivot as nvarchar(max), @comando_sql  as nvarchar(max)
set @colunas_pivot =
    stuff((
        select
            distinct ',' + quotename(month(Mes_Ano))
        from RESUMO_TECNICOS_HISTORICO_ATUAL 
        for xml path('')
    ), 1, 1, '')
print @colunas_pivot
set @comando_sql = '
    select * from (
        select
            month(mes_ano) as Ano,
            nome,
            sum(qra) as Valor
        from RESUMO_TECNICOS_HISTORICO_ATUAL where year(mes_ano) = 2018
                group by nome, month(Mes_Ano) 
    ) em_linha
    pivot (sum(valor) for ano in (' + @colunas_pivot + ')) em_colunas 
    order by 1'

execute(@comando_sql)

Answer 1

Edvaldo,

Já tentou declarar o comando Desc ou Asc no Order By?

Outra possibilidade, declare no seu Pivot a sequência dos meses, caso contrário SQL Server vai definir em tempo de execução a própria ordem de acordo com a condição aplicado no Pivot.

Veja se este exemplo te ajuda:

DECLARE @Mes INT, @Ano INT

SELECT @Mes = 8, @Ano = 2010

;WITH CTE (Data) AS
(
  SELECT DATEADD(year, @Ano - 1900, DATEADD(month, @Mes - 1, 0))
  UNION ALL
  SELECT Data+1
  FROM CTE
  WHERE MONTH(Data + 1) = @Mes
)

SELECT
  [1] AS [Domingo]
, [2] AS [Segunda-Feira]
, [3] AS [Terça-Feira]
, [4] AS [Quarta-Feira]
, [5] AS [Quinta-Feira]
, [6] AS [Sexta-Feira]
, [7] AS [Sábado]
FROM 
 (
	SELECT DAY(Data) AS Dia, DATEPART(weekday, Data) DiaSemana, DATEPART(week, Data) Semana
	FROM CTE
 ) AS Datas
PIVOT
(
	MAX(Dia) FOR DiaSemana
	IN ([1], [2], [3], [4], [5], [6], [7])
) AS A

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 2

Junior,

Tentei sim, mas apresenta um erro

[11],[6],[8],[7],[1],[3],[10],[4],[9],[12],[2],[5]
Mensagem 1033, Nível 15, Estado 1, Linha 9
A cláusula ORDER BY é inválida em exibições, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP, OFFSET ou FOR XML também esteja especificado.

não declarei a sequência dos meses, pois quero utilizar o PIVOT dinâmico... 

Answer 3

Boa tarde,

Edvaldo, experimente fazer uns testes dessa forma:

set @colunas_pivot =
    stuff((
        select
            ',' + quotename(Mes)           
        from 
        (
            select 
                distinct month(Mes_Ano) as Mes
            from RESUMO_TECNICOS_HISTORICO_ATUAL
        ) as t
        for xml path('')
    ), 1, 1, '')

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 4

Deleted

Answer 5

Edvaldo, eis solução sem o uso de comando SQL dinâmico:

-- código #1     © José Diz/BH
-- informe o ano
declare @Ano smallint;
set @Ano= 2018;

-- variáveis de período anual (garante sargability)
declare @De date, @Até date;
set dateformat dmy;
set @De= cast ('1/1/' + cast (@Ano as char(4)) as date);
set @Até= cast ('31/12/' + cast (@Ano as char(4)) as date);

--
with Resumo_ano as (
SELECT nome, qra, month(mes_ano) as Mês
  from RESUMO_TECNICOS_HISTORICO_ATUAL 
  where mes_ano between @De and @Até
)
SELECT nome, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
  from Resumo_ano
       pivot (sum (qra) for Mês in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) as P;

Como está declarada a coluna mes_ano?

Observe que foram criadas 2 variáveis, @De e @Até, para conter o período de seleção. O motivo está descrito no artigo “Construindo códigos T-SQL eficientes: Sargability”.

---

José Diz     Belo Horizonte, MG - Brasil     [Linked In]   [e-mail]   [Porto SQL]

---

Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

Está declarada como Datetime

Answer 6

José,

Sem querer abusar da sua ajuda ... Como eu faço para colocar uma coluna Total somando os valores dos meses ?

Answer 7

Deleted

Answer 8

José,

Sem querer abusar da sua ajuda ... Como eu faço para colocar uma coluna Total somando os valores dos meses ?

Não sei se é a melhor forma, mas consegui fazer assim o total:

ALTER PROCEDURE GERA_HISTORICO_RECS_DESIGNACAO @VAR_ANO INT
AS BEGIN

declare @Ano smallint;
set @Ano= @VAR_ANO;

-- variáveis de período anual (garante sargability)
declare @De date, @Até date;
set dateformat dmy;
set @De= cast ('1/1/' + cast (@Ano as char(4)) as date);
set @Até= cast ('31/12/' + cast (@Ano as char(4)) as date);

with Resumo_ano as (
SELECT designacao_circuito, nome_guerra,num_rec,  month(dt_hr_fecom) as Mês
  from BASE_RREIP 
  where dt_hr_fecom between @De and @Até 
  
),
 Resumo_ano2 as(
SELECT designacao_circuito,nome_guerra as Cliente,  [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
  from Resumo_ano 
       pivot (count (num_rec) for Mês in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) as P
)

select TOP 20 designacao_circuito, Cliente,  
		   [1]+ [2]+ [3]+ [4]+ [5]+ [6]+ [7]+ [8]+ [9]+ [10]+ [11]+ [12] as Total,
		   [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
  from Resumo_ano2 
		ORDER BY [1]+ [2]+ [3]+ [4]+ [5]+ [6]+ [7]+ [8]+ [9]+ [10]+ [11]+ [12] DESC
END

obs.: Os nomes das colunas e tabela mudaram, porque inicialmente estava usando uma tabela de testes.

Answer 9

Deleted

Answer 10

Obrigado !!