Usuário com melhor resposta
PIVOT - Ordenar as colunas

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)
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]
-
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...
-
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
-
-
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
-
-
-
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 .
-
-