none
PIVOT - Ordenar as colunas RRS feed

  • Pergunta

  • 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)
    

    terça-feira, 8 de janeiro de 2019 18:45

Respostas

Todas as Respostas

  • 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]

    terça-feira, 8 de janeiro de 2019 18:54
  • 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... 

    terça-feira, 8 de janeiro de 2019 19:02
  • 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

    terça-feira, 8 de janeiro de 2019 20:06
  • Deleted
    • Marcado como Resposta Edvaldo A quarta-feira, 9 de janeiro de 2019 11:14
    terça-feira, 8 de janeiro de 2019 20:21
  • 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

    quarta-feira, 9 de janeiro de 2019 10:54
  • José,

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


    • Editado Edvaldo A quarta-feira, 9 de janeiro de 2019 11:31 correção do texto
    quarta-feira, 9 de janeiro de 2019 11:20
  • Deleted
    quarta-feira, 9 de janeiro de 2019 11:21
  • 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.


    • Editado Edvaldo A quarta-feira, 9 de janeiro de 2019 12:07 .
    quarta-feira, 9 de janeiro de 2019 12:05
  • Deleted
    quarta-feira, 9 de janeiro de 2019 13:28
  • Obrigado !!
    quarta-feira, 9 de janeiro de 2019 13:58