none
Dúvidas com PIVOT RRS feed

  • Pergunta

  • Boa tarde!

    Estou quebrando a cabeça tentando fazer uma query usando o PIVOT, mas, apesar de achar que entendi o comando, ela não está funcionando.  Então, com certeza sou eu que estou fazendo besteira... 

    O que eu fiz foi o seguinte, só que ele retorna zero em todas as colunas:

    Code Block

        SELECT    bairro_codigo,
                ['-'] AS Abertas,
                ['E'] AS EmAndamento,
                ['A'] AS Atendidas,
                ['D'] AS Depredacoes,
                ['I'] AS Improcedentes,
                ['R'] AS Repetidas,
                ['P'] AS Problemas
        FROM
            (SELECT bairro_codigo, situacao_codigo, codigo
             FROM solicitacoes
             WHERE municipio_codigo = @municipio_codigo AND MONTH(data_geracao) = @mes AND YEAR(data_geracao) = @ano) As SolicitacoesSource
        PIVOT (
            COUNT(codigo)
            FOR situacao_codigo IN (['-'],['E'],['A'],['D'],['I'],['R'],['P'])
        ) AS SolicitacoesPivot


    O campo situacao_codigo é um CHAR(1), bairro_codigo e codigo são INTcodigo é a PK de solicitacoes.

    O que eu estou querendo é categorizar de forma que, para cada bairro, ele vai exibir quantas solicitações estão em cada situação.

    Só consegui resolver fazendo a seguinte gambiarra solução alternativa emergencial:

    Code Block

    SELECT bairro_codigo,
           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = '-' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS Abertas,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'E' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS EmAndamento,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'A' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS Atendidas,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'D' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS Depredacao,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'I' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS Improcedentes,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'R' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo)
    AS Repetidas,

           (SELECT COUNT(codigo)

    FROM solicitacoes
    WHERE situacao_codigo = 'P' AND bairro_codigo = s.bairro_codigo AND MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo) AS Problemas,

           COUNT(codigo) AS Total
    FROM  solicitacoes s
    WHERE MONTH(data_geracao)=@mes AND YEAR(data_geracao)=@ano AND municipio_codigo = @municipio_codigo
    GROUP BY bairro_codigo
    ORDER BY bairro_codigo


    Ou seja, muuuito longe do ideal.

    Onde estou errando?


    Valeu,


    Anderson



    terça-feira, 8 de janeiro de 2008 16:22

Respostas

  • Boa Tarde Anderson,

     

    Para ser sincero eu não consideraria uma "gambiarra" sua segunda solução. Ela só é um pouco mais trabalhosa mas até o SQL Server 2000 era uma solução bem viável. O Pivot facilitou a sintaxe, mas o desempenho dele não é muito diferente de sua solução alternativa. Além do mais, o Pivot tem uma forte limitação que é a necessidade de se conhecer previamente os valores que irão compor as colunas. Essa limitação na minha opinião joga por terra a baixo todo o Marketing que se fez em torno desse operador.

     

    No seu caso, acho que mesmo colunas do tipo string devem ficar sem aspas na cláusula IN dentro da Query Pivot. Tente fazer o seguinte:

     

    Code Block

    SELECT bairro_codigo,

    [-] AS Abertas,

    [E] AS EmAndamento,

    [A] AS Atendidas,

    [D] AS Depredacoes,

    [I] AS Improcedentes,

    [R] AS Repetidas,

    [P] AS Problemas

    FROM

    (SELECT bairro_codigo, situacao_codigo, codigo

    FROM solicitacoes

    WHERE municipio_codigo = @municipio_codigo AND MONTH(data_geracao) = @mes AND YEAR(data_geracao) = @ano) As SolicitacoesSource

    PIVOT (

    COUNT(codigo)

    FOR situacao_codigo IN ([-],[E],[A],[D],[I],[R],[P])

    ) AS SolicitacoesPivot

     

     

    [ ]s,

     

    Gustavo

    terça-feira, 8 de janeiro de 2008 17:00

Todas as Respostas

  • Boa Tarde Anderson,

     

    Para ser sincero eu não consideraria uma "gambiarra" sua segunda solução. Ela só é um pouco mais trabalhosa mas até o SQL Server 2000 era uma solução bem viável. O Pivot facilitou a sintaxe, mas o desempenho dele não é muito diferente de sua solução alternativa. Além do mais, o Pivot tem uma forte limitação que é a necessidade de se conhecer previamente os valores que irão compor as colunas. Essa limitação na minha opinião joga por terra a baixo todo o Marketing que se fez em torno desse operador.

     

    No seu caso, acho que mesmo colunas do tipo string devem ficar sem aspas na cláusula IN dentro da Query Pivot. Tente fazer o seguinte:

     

    Code Block

    SELECT bairro_codigo,

    [-] AS Abertas,

    [E] AS EmAndamento,

    [A] AS Atendidas,

    [D] AS Depredacoes,

    [I] AS Improcedentes,

    [R] AS Repetidas,

    [P] AS Problemas

    FROM

    (SELECT bairro_codigo, situacao_codigo, codigo

    FROM solicitacoes

    WHERE municipio_codigo = @municipio_codigo AND MONTH(data_geracao) = @mes AND YEAR(data_geracao) = @ano) As SolicitacoesSource

    PIVOT (

    COUNT(codigo)

    FOR situacao_codigo IN ([-],[E],[A],[D],[I],[R],[P])

    ) AS SolicitacoesPivot

     

     

    [ ]s,

     

    Gustavo

    terça-feira, 8 de janeiro de 2008 17:00
  • Valeu, Gustavo!

    É isso que dá não testar todas as alternativas: eu tinha testado com os colchetes e aspas simples e só com as aspas simples.  Ficou faltando só com os colchetes!

    Funcionou 100%.

    Quanto à limitação do PIVOT, eu também já tinha pensado nisso.  Enquanto persistir esse tipo de problema, queries dinâmicas nunca deixarão de ser aquelas geradas como string e executadas num EXEC(@sql) da vida, ou seja, gambiarra...

    A gente tenta fazer direito, mas a Microsoft empurra a gente pra essas coisas!


    Abraço,


    Anderson
    terça-feira, 8 de janeiro de 2008 17:24
  • Anderson,

     

    Mas é importante entender que este tipo de situação em utilizar query dinamicas pode ajudar em muito, mas é também a arma para os SQL Injections da vida.

     

    Isso poderia representar muitos problemas de segurança dentro de uma aplicação mal escrita, principalmente se estiver acessando dados no SQL Server 2000, já o SQL Server 2005 o suporte a este tipo de procedimento foi melhorado com isso o nível de segurança aumentou.

     

    Em particular eu também não vejo como uma gambiarra.

     

    No caso de utilizar Pivot, como já foi destacado trata-se de um procedimento que consumo muito tempo de processamento e principalmente memória.

     

    terça-feira, 8 de janeiro de 2008 17:35