none
Ajuda PIVOT RRS feed

  • Pergunta

  • Amigos,

    Estou tentando executar o comando abaixo e não estou conseguindo. Porém quando troco o paramento [Jun 2 2009] para [02/06/2009] funciona corretamente. Poderiam me ajudar?

    Código:
    --Captura MenorPeriodo
    Declare @menorPeriodo as SmallDateTime
    --Set @menorPeriodo = (Select Min(Convert (SmallDateTime,Cast(Dt_Relatorio as Char(11)),103)) as Dt_Relatorio From dbo.D_Pontual_CR_Relatorio_Oi)
    Set @menorPeriodo = ('2009-06-02')
    --Captura MaiorPeriodo
    Declare @maiorPeriodo as SmallDateTime
    --Set @maiorPeriodo = (Select Max(Convert (SmallDateTime,Cast(Dt_Relatorio as Char(11)),103)) as Dt_Relatorio From dbo.D_Pontual_CR_Relatorio_Oi)
    Set @maiorPeriodo = ('2009-06-03')
    --Inicializo periodo
    Declare @Periodos as VARCHAR(Max)
    Set @Periodos = ''
    -- Inicializa a variável @cmdSQL com a montagem do PIVOT
    -- O caractér ? será substituído pelo período obtido dinamicamente
    Declare @cmdSQL VARCHAR(Max)
    SET @cmdSQL = ('''SELECT Fx_Atraso, ?
    FROM (SELECT Fx_Atraso, Dt_Relatorio_Teste, Cast(Sum(Sld_Devedor) as Money) as Vl_Sld_Devedor 
    FROM dbo.D_Pontual_CR_Relatorio_Oi 
    Where UN In(''Varejo'', ''Varejo/Oi Conta Colaborador'', ''VARME'', ''VARRES'', ''VARRES/Oi Conta Colaborador'', ''VARRES/TOP'') Group By Fx_Atraso, Dt_Relatorio) AS TBO 
    PIVOT (Sum(Vl_Sld_Devedor) FOR Dt_Relatorio_Teste In (?)) AS TPVT''')
    --Select @cmdSQL
    -- Montagem dos períodos
    WHILE @menorPeriodo <= @maiorPeriodo
    BEGIN
    SET @Periodos = @Periodos + '[' + CAST(@menorPeriodo AS CHAR(11)) + '],'
    SET @menorPeriodo = @menorPeriodo + 1
    END 
    -- Monta os períodos
    SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1) 
    -- Substitui o ? pelo período montado dinamicamente
    SET @cmdSQL = REPLACE(@cmdSQL,'?',@Periodos)
    -- Executa o comando, opcionalmente dê um PRINT
    PRINT @cmdSQL
    EXEC (@cmdSQL)

    Esse é o print do comando:

     


    'SELECT Fx_Atraso, [Jun 2 2009],[Jun 3 2009]
    FROM (SELECT Fx_Atraso, Dt_Relatorio_Teste, Cast(Sum(Sld_Devedor) as Money) as Vl_Sld_Devedor 
    FROM dbo.D_Pontual_CR_Relatorio_Oi 
    Where UN In('Varejo', 'Varejo/Oi Conta Colaborador', 'VARME', 'VARRES', 'VARRES/Oi Conta Colaborador', 'VARRES/TOP') Group By Fx_Atraso, Dt_Relatorio) AS TBO 
    PIVOT (Sum(Vl_Sld_Devedor) FOR Dt_Relatorio_Teste In ([Jun 2 2009],[Jun 3 2009])) AS TPVT'


    Daniel
    segunda-feira, 22 de junho de 2009 17:39

Respostas

  • Boa Tarde,

    Essa necessidade é um pouco atípica, mas é possível com alguns truques no seu algoritmo.

    WHILE @menorPeriodo <= @maiorPeriodo
    BEGIN
     IF EXISTS (SELECT * FROM D_Pontual_CR_Relatorio_Oi WHERE Dt_Relatorio = @menorPeriodo)
     BEGIN
      SET @Periodos = @Periodos + '[' + CAST(@menorPeriodo AS CHAR(11)) + '],'
     END
     SET @menorPeriodo = @menorPeriodo + 1
    END


    Assim, a montagem da coluna só irá acontecer quando houver pelo menos um registro na data especificada. Se suas necessidades de consulta começarem a evoluir dessa maneira, cogite seriamente em utilizar outra tecnologia (OWC, Reporting Services, Integration Services, etc). Banco de dados tradicional não é pra isso...

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:03
    • Não Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:03
    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 23 de junho de 2009 17:22
    • Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:45
    terça-feira, 23 de junho de 2009 16:15

Todas as Respostas

  • DrAlves,

    Mas porque você deseja passar o valor neste formato Jun 2 2009?


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    segunda-feira, 22 de junho de 2009 18:06
  • Junior,

    consegui acertar o formato e a consulta rodou perfeitamente. O formato foi 02/06/2009.

    Porém tenho um dúvida. Como faço para excluir as colunas que possuem valores null. No caso seriam as colunas ( 14/05/2009
    , 15/05/2009)

    Vide exemplo:

    Fx_Atraso 13-05-2009 14-05-2009 15-05-2009
    NULL 136,81 NULL NULL
    00 - A Vencer 92605,43 NULL NULL
    01-01-1930 159424145 NULL NULL
    02 - 31 - 60 98057545,7 NULL NULL
    03 - 61 - 90 28825464,62 NULL NULL
    04 - 91 - 120 36525109,21 NULL NULL
    05 - 121 - 150 16794331,26 NULL NULL
    06 - 151 - 180 12660473,32 NULL NULL
    07 - 181 - 210 11040011,42 NULL NULL
    08 - >210 324319889,6 NULL NULL

    Daniel
    segunda-feira, 22 de junho de 2009 18:54
  • DrAlves,

    Você deseja não exibir estas colunas quando elas possuirem valores nulos ou realmente deseja remover sua exibição?


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    segunda-feira, 22 de junho de 2009 20:02
  • Desejo excluir as colunas que estão nulas.
    Daniel
    segunda-feira, 22 de junho de 2009 20:05
  • Conseguiu ver alguma coisa?
    Daniel
    segunda-feira, 22 de junho de 2009 20:57
  • Marcelo bom dia,

    conseguiu ver alguma coisa?

    Desde já agradeço.
    Daniel
    terça-feira, 23 de junho de 2009 12:24
  • Bom dia Galera,

    Alguem poderia ajudar?

    Desde já agradeço.
    Daniel
    terça-feira, 23 de junho de 2009 13:54
  • Ola Daniel,

    Daniel tentei algumas formas p/ eliminar esta coluna mas nenhuma deu certo... é facil eliminar linha qndo a linha é nula mas eliminar coluna quando a linha é nula é mais complexo :)

    Tente eliminar esta coluna antes do pivot (qndo ela é linha ainda) acredito que vc pode obter algum sucesso.

    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 23 de junho de 2009 14:26
  • Mais essa coluna ainda não existe como o meu Pivot é dinamico, ou seja, eu pego a menor data e a maior data todo esse intervalo pode vir null, sendo assim, não tem como eliminar antes.

    Andei pesquisando na Net e o Gustavo tem dois post muito bons, mais infelizmente não aborda nada com relação a isso.

    Gustavo poderia me ajudar?

    Marcelo obrigado pela ajuda.
    Daniel
    terça-feira, 23 de junho de 2009 14:35
  • Boa Tarde,

    Essa necessidade é um pouco atípica, mas é possível com alguns truques no seu algoritmo.

    WHILE @menorPeriodo <= @maiorPeriodo
    BEGIN
     IF EXISTS (SELECT * FROM D_Pontual_CR_Relatorio_Oi WHERE Dt_Relatorio = @menorPeriodo)
     BEGIN
      SET @Periodos = @Periodos + '[' + CAST(@menorPeriodo AS CHAR(11)) + '],'
     END
     SET @menorPeriodo = @menorPeriodo + 1
    END


    Assim, a montagem da coluna só irá acontecer quando houver pelo menos um registro na data especificada. Se suas necessidades de consulta começarem a evoluir dessa maneira, cogite seriamente em utilizar outra tecnologia (OWC, Reporting Services, Integration Services, etc). Banco de dados tradicional não é pra isso...

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:03
    • Não Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:03
    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 23 de junho de 2009 17:22
    • Marcado como Resposta dralves terça-feira, 23 de junho de 2009 17:45
    terça-feira, 23 de junho de 2009 16:15
  • Blz Gustavo,

    mais uma vez sua ajuda foi essencial. Tb Gostaria de agradecer ao Marcelo.

    Uma ultima ajuda.

    Existe a possibilidade de renomear uma coluna em especial, ou seja, conforme o PIVOT a coluna irá se chamar 13/05/2009 porém gostaria de chamar de Fechamento_Abril_2009. Isso é possível?
    Daniel
    terça-feira, 23 de junho de 2009 17:03
  • Olá Daniel,

    Possível é. Bastaria que você adicionasse um Alias nas colunas. O problema é que o comando vai ficar ainda mais complexo.
    Outra opção é persistir os dados em uma tabela temporária e posteriormente renomear as colunas com um cursor e a sp_rename.

    Não sei a freqüência com que esse código será executado, mas é visível que a complexidade está aumentando significativamente. No futuro poderá ficar bem complicado de manter. Sugiro avaliar outras soluções.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry
    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de junho de 2009 17:22
  • Ele é executado uma unica vez na semana e possui uma quantidade de 3.000 registros.

    O eu fiz pra renomear foi carregar em uma tabela temporaria e renomear na mão, porém gostaria de deixar a consulta automatizada, ou seja, eu tenho que renomear somente a coluna que possui a menor data de vencimento para cada mês. Vide exemplo:

    Fx_Atraso 13-05-2009 02-06-2009 09-06-2009 18-06-2009 23-06-2009 Unid
    NULL  NULL                            1.114                         10.519                         91.350                         91.350 Empresarial
    00 - A Vencer                       405.168                  24.717.777                       448.355                    8.542.084                    8.542.084 Empresarial
    01 - 0 - 30                  38.568.790                  10.487.249                  28.440.217                  40.527.700                  32.528.038 Empresarial

    Gostaria de pegar a primeira data de mai/2009(13-05-2009) e renomear para Fechamento_Abril_2009 e pegar a data de jun/2009(02-06-2009) e renomear para Fechamento_Maio_2009.

    Segue código como está hj.

     

    If exists(Select * From sysobjects Where Name = 'D_Pontual_CR_Relatorio_Fixa_Dinamica')
    Drop Table D_Pontual_CR_Relatorio_Fixa_Dinamica;
     
    --Captura MenorPeriodo
    Declare @menorPeriodo as SmallDateTime
    Set @menorPeriodo = (Select Min(Convert (SmallDateTime,Cast(Dt_Relatorio as Char(11)),103)) as Dt_Relatorio From dbo.D_Pontual_CR_Relatorio_Oi)
    --Set @menorPeriodo = ('2009-06-02')
    --Select CAST(@menorPeriodo AS CHAR(11))
    --Captura MaiorPeriodo
    Declare @maiorPeriodo as SmallDateTime
    Set @maiorPeriodo = (Select Max(Convert (SmallDateTime,Cast(Dt_Relatorio as Char(11)),103)) as Dt_Relatorio From dbo.D_Pontual_CR_Relatorio_Oi)
    --Set @maiorPeriodo = ('2009-06-03')
    --Inicializo periodo
    Declare @Periodos as VARCHAR(Max)
    Set @Periodos = ''
    -- Inicializa a variável @cmdSQL com a montagem do PIVOT
    -- O caractér ? será substituído pelo período obtido dinamicamente
    Declare @cmdSQL VARCHAR(Max)
    SET @cmdSQL = ('SELECT Fx_Atraso, ?,
    Case When UN In(''Varejo'', ''Varejo/Oi Conta Colaborador'', ''VARME'', ''VARRES'', ''VARRES/Oi Conta Colaborador'', ''VARRES/TOP'') Then ''Varejo''
    When UN In(''EMP'', ''EMP/Oi Conta Colaborador'') Then ''Empresarial''
    When UN In(''GDC'', ''GDC/Oi Conta Colaborador'', ''KAC'', ''KAC/BRONZE'', ''KAC/BRONZE-CORP'', ''KAC/DIAMANTE'',''KAC/DIAMANTE-CORP'',''KAC/DIAMANTE-OUT'', ''KAC/NOVO CLIENTE'', ''KAC/OURO-CORP'',''KAC/PLATINA-CORP'', ''KAC/PRATA'', ''KAC/PRATA-CORP'', ''KAC/PRATA-GDC2'') Then ''Corporativo''
    Else ''Outros'' End as UN
    Into D_Pontual_CR_Relatorio_Fixa_Dinamica 
    FROM (SELECT Fx_Atraso, Dt_Relatorio, UN, Cast(Sum(Sld_Devedor) as Money) as Vl_Sld_Devedor 
    FROM dbo.D_Pontual_CR_Relatorio_Fixa 
    Group By Fx_Atraso, Dt_Relatorio, UN) AS TBO 
    PIVOT (Sum(Vl_Sld_Devedor) FOR Dt_Relatorio In (?)) AS TPVT')
    --Select @cmdSQL
    -- Montagem dos períodos
    --WHILE @menorPeriodo <= @maiorPeriodo
    --BEGIN
    -- SET @Periodos = @Periodos + '[' + RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),10) + '],'
    -- SET @menorPeriodo = @menorPeriodo + 1
    --END 
    WHILE @menorPeriodo <= @maiorPeriodo
    BEGIN
    IF EXISTS (SELECT * FROM D_Pontual_CR_Relatorio_Oi WHERE Dt_Relatorio = RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),10))
    BEGIN
    SET @Periodos = @Periodos + '[' + RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),10) + '],'
    END
    SET @menorPeriodo = @menorPeriodo + 1
    END
     
    --Select @Periodos
    -- Monta os períodos
    SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1) 
     
    -- Substitui o ? pelo período montado dinamicamente
    SET @cmdSQL = REPLACE(@cmdSQL,'?',@Periodos)
    -- Executa o comando, opcionalmente dê um PRINT
    --PRINT @cmdSQL
    EXEC (@cmdSQL)

     

     


    Daniel
    terça-feira, 23 de junho de 2009 17:44
  • Gustavo,

    conseguiu ver alguma coisa?
    Daniel
    terça-feira, 23 de junho de 2009 19:04
  • Olá Daniel,

    Eu até tentei, mas além do código ficar absurdamente complexo, descobri que não é possível renomear colunas de tabelas temporárias.

    create table #t (
     [13-05-2009] int,
     [14-05-2009] int,
     [04-06-2009] int,
     [07-06-2009] int)
    set language Brazilian
    declare @i int, @f int, @col varchar(20), @cmd varchar(1000)
    ;With Datas As (
    select Name As Col, CAST(Name As SmallDateTime) As Data from tempdb.sys.columns
    where OBJECT_NAME(object_id,DB_ID('TempDb')) like '#t%'),
    Res As (
    select Col, Data, MONTH(Data) As Mes From Datas),
    Sel As (
    select Col, Data, Mes,
    ROW_NUMBER() Over (Partition By Mes Order By Data) As Pos
    from res)
    select @f = COUNT(*) from Sel Where Pos = 1
    set @i = 1
    set @col = ''
    while @i <= @f
    begin
     ;With Datas As (
     select Name As Col, CAST(Name As SmallDateTime) As Data from tempdb.sys.columns
     where OBJECT_NAME(object_id,DB_ID('TempDb')) like '#t%'),
     Res As (
     select Col, Data, MONTH(Data) As Mes From Datas),
     Sel As (
     select Col, Data, Mes,
     ROW_NUMBER() Over (Partition By Mes Order By Data) As Pos
     from res),
     
     Rel As (
     select Col, Data, Mes, Pos,
     ROW_NUMBER() Over (Order By Col) As Num
     from Sel where Pos = 1)
     select top 1 @cmd = 'Comando para substituir a coluna ' +
      COL + ' para Fechamento_' + DATENAME(M,DATEADD(M,-1,Data)) + '2009'
     from Rel
     where pos = 1 and Num = @i
     
     print @cmd
     
     set @col = (select top 1 Name from tempdb.sys.columns
     where OBJECT_NAME(object_id,DB_ID('TempDb')) like '#t%' and name > @col)
      
     set @i = @i + 1
      
    end
    drop table #t

    Veja que essa complexidade foi só para renomear as colunas de uma tabela temporária (se pudéssemos usar a sp_rename). Adicione esse código no código anterior e realmente teremos uma solução muito complexa. Reforço que você deve buscar outras soluções, pois, a SQL não é para isso.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry


    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de junho de 2009 19:07
  • A minha tabela não é temporaria. Isso já facilitaria um pouco?
    Daniel
    terça-feira, 23 de junho de 2009 19:16
  • Olá Daniel,

    Você teria que adaptar o meu código com a sp_rename. Essa procedure pode renomear colunas. Então você faria um sp_rename com o nome antigo e o nome novo que podem ser obtidos a partir do meu código (nossa dá até pregüiça (rs)).

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry
    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de junho de 2009 20:25
  • Tem algum exemplo utilizando o sp_rename?

    Caso não tenha desde já agradeço pela ajuda.


    Daniel
    terça-feira, 23 de junho de 2009 20:32
  • Olá Daniel,

    O Books OnLine tem o exemplo da sp_rename para renomear a coluna.
    Você poderá utilizá-lo.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como importar e exportar imagens entre o SQL Server e o File System ? – Parte II
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!612.entry
    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de junho de 2009 20:43