none
PIVOT Com soma de 2 colunas, como eu faço? RRS feed

  • Pergunta

  •  

    Ola

    tenho esta view

     

    SELECT BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoQtd, BNCCttMunProdAnoAAnoVal

    FROM VWBNCCttMunProdAnoAAno

     

     

    e fiz esse pivot

     

    SELECT BNCCttMunIDProdAnoAAno ,

    [2007] AS BNCCttMunProdAno2007Qtd

    FROM

    ( SELECT BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoQtd

    FROM VWBNCCttMunProdAnoAAno

    ) AS SourceTable

    PIVOT

    (

    SUM(BNCCttMunProdAnoAAnoQtd)

    FOR BNCCttMunProdAnoDt IN ([2007])

    )

    AS PivotTable

     

     

    porem, alem de somar a quantidade, eu queria somar tambem os valores

    {

    SELECT BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoQtd, BNCCttMunProdAnoAAnoVal

    FROM VWBNCCttMunProdAnoAAno

    }

     

    porem, eu nao consegui, tentei varias coisas e nao deu muito certo

    é possivel fazer isso em apenas um query sem ter que fazer um inner Join com outro Pivot com a soma dos Valores?

     

    por enqto é so

    abs

    T+

     

    segunda-feira, 7 de janeiro de 2008 20:26

Respostas

  • Boa Noite,

     

    Segundo a documentação do Books OnLine isso não seria possível de ser feito.

     

     <aggregation function>( <column being aggregated> )

     

    Essa descrição deixa a entender que apenas uma coluna pode ser agregada. Nesse caso seria necessário sim, realizar outro PIVOT para realizar a soma de valores ou então esquecer o PIVOT e realizar isso com subqueries ou cursores.

     

    Se você se vê na necessidade de realizar constantes "pivoteamentos" considere utilizar uma solução baseada em Analysis Services ao invés de recorrer ao Transact SQL.

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 7 de janeiro de 2008 21:01

Todas as Respostas

  • Boa Noite,

     

    Segundo a documentação do Books OnLine isso não seria possível de ser feito.

     

     <aggregation function>( <column being aggregated> )

     

    Essa descrição deixa a entender que apenas uma coluna pode ser agregada. Nesse caso seria necessário sim, realizar outro PIVOT para realizar a soma de valores ou então esquecer o PIVOT e realizar isso com subqueries ou cursores.

     

    Se você se vê na necessidade de realizar constantes "pivoteamentos" considere utilizar uma solução baseada em Analysis Services ao invés de recorrer ao Transact SQL.

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 7 de janeiro de 2008 21:01
  • Boa noite Gustavo
    Eu tinha visto isso, mas nao tinha certeza se era isso mesmo, mas infelizmente nao pode!

    Sobre o Analysis Services, poderia me indicar alguma coisa ou me mostrar um exemplo.

    Acho que vou ficar no Inner Join, suponho que das opcoes, essa seja a que consome menos recurso da maquina, estou certo?

    obrigado pela ajuda
    T+

    segunda-feira, 7 de janeiro de 2008 21:06
  • Olá Krusst,

     

    O padrão ANSI99 (SQL99 ou SQL3) trouxe algumas capacidades OLAP para a SQL, mas mesmo "quebrando um galho danado" essas funcionalidades são (e devem ser) limitadas. No entanto, o fato de haver limitações não elimina muitas necessidades que aparecem principalmente em relatórios.

     

    O que quis dizer é que se você precisa muitas vezes elaborar relatórios de sumarização, utilizar funções PIVOT, dezenas de GROUPs By, SQL dinâmicas, etc em excesso, é porque sua necessidade pode estar um pouco além do que o Transact SQL pode oferecer. Para esse tipo de situação é desejável que você utilize o Analysis Services que é um produto mais voltado para esse tipo de necessidade (entre muitas outras).

     

    Eu não poderia demonstrá-lo aqui já envolveria ter uma base de dados, projetar alguns cubos e demonstrar as capacidades de pivoteamento que ele possui. Você poderá encontrar no Books OnLine alguns tutoriais de Analysis Services. Basta procurar por Tutorials e em seguida Analysis Services. São arquivos de exemplo do próprio SQL Server 2005.

     

    Não estou dizendo que o Analysis Services é a saída. Provavelmente você vai conseguir resolver por outra forma (como você mesmo já havia sugerido). Depois de dar uma olhada nos tutorias do Analysis Services é que vale a pena avaliar até que ponto ele poderá ajudá-lo.

     

    [ ]s,

     

    Gustavo

    segunda-feira, 7 de janeiro de 2008 21:29
  • Blz gustavo, agora entendi.
    Essa parte eu mais ou menos utlizo, tenho alguns "indices" que toda vez que o sistema é atualizado(recebo um .txt com os contratos do sistema) ele gera com a data da atualização.

     

    Para o problema que disse agora a pouco, fiz da seguinte maneira, se alguem tiver uma dica pra melhorar, sera bem vinda.

     

    abs

    T+

     

    ALTER VIEW [dbo].[VWBNCCttMunProdAnoAAnoPvtQtd]

    AS

    SELECT BNCCttTipIDProdAnoAAno,

    BNCCttMunIDProdAnoAAno ,

    ISNULL([1999],0) AS BNCCttMunProd1999Qtd,

    ISNULL([2000],0) AS BNCCttMunProd2000Qtd,

    ISNULL([2001],0) AS BNCCttMunProd2001Qtd,

    ISNULL([2002],0) AS BNCCttMunProd2002Qtd,

    ISNULL([2003],0) AS BNCCttMunProd2003Qtd,

    ISNULL([2004],0) AS BNCCttMunProd2004Qtd,

    ISNULL([2005],0) AS BNCCttMunProd2005Qtd,

    ISNULL([2006],0) AS BNCCttMunProd2006Qtd,

    ISNULL([2007],0) AS BNCCttMunProd2007Qtd

    FROM

    ( SELECT BNCCttTipIDProdAnoAAno,BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoQtd

    FROM VWBNCCttMunProdAnoAAno

    ) AS SourceTable

    PIVOT

    (

    SUM(BNCCttMunProdAnoAAnoQtd)

    FOR BNCCttMunProdAnoDt IN ([1999],[2000],[2001],[2002],[2003],[2004],[2005], [2006], [2007])

     

    ) AS PivotTable

     

     

    ALTER VIEW [dbo].[VWBNCCttMunProdAnoAAnoPvtEmprestVal]

    AS

    SELECT BNCCttTipIDProdAnoAAno,

    BNCCttMunIDProdAnoAAno ,

    ISNULL([1999],0) AS BNCCttMunProd1999EmprestVal,

    ISNULL([2000],0) AS BNCCttMunProd2000EmprestVal,

    ISNULL([2001],0) AS BNCCttMunProd2001EmprestVal,

    ISNULL([2002],0) AS BNCCttMunProd2002EmprestVal,

    ISNULL([2003],0) AS BNCCttMunProd2003EmprestVal,

    ISNULL([2004],0) AS BNCCttMunProd2004EmprestVal,

    ISNULL([2005],0) AS BNCCttMunProd2005EmprestVal,

    ISNULL([2006],0) AS BNCCttMunProd2006EmprestVal,

    ISNULL([2007],0) AS BNCCttMunProd2007EmprestVal

    FROM

    ( SELECT BNCCttTipIDProdAnoAAno,BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoEmprestVal

    FROM VWBNCCttMunProdAnoAAno

    ) AS SourceTable

    PIVOT

    (

    SUM(BNCCttMunProdAnoAAnoEmprestVal)

    FOR BNCCttMunProdAnoDt IN ([1999],[2000],[2001],[2002],[2003],[2004],[2005], [2006], [2007])

     

    ) AS PivotTable

    ALTER VIEW [dbo].[VWBNCCttMunProdAnoAAnoPvtCttVal]

    AS

    SELECT BNCCttTipIDProdAnoAAno,

    BNCCttMunIDProdAnoAAno ,

    ISNULL([1999],0) AS BNCCttMunProd1999CttVal,

    ISNULL([2000],0) AS BNCCttMunProd2000CttVal,

    ISNULL([2001],0) AS BNCCttMunProd2001CttVal,

    ISNULL([2002],0) AS BNCCttMunProd2002CttVal,

    ISNULL([2003],0) AS BNCCttMunProd2003CttVal,

    ISNULL([2004],0) AS BNCCttMunProd2004CttVal,

    ISNULL([2005],0) AS BNCCttMunProd2005CttVal,

    ISNULL([2006],0) AS BNCCttMunProd2006CttVal,

    ISNULL([2007],0) AS BNCCttMunProd2007CttVal

    FROM

    ( SELECT BNCCttTipIDProdAnoAAno,BNCCttMunIDProdAnoAAno, BNCCttMunProdAnoDt, BNCCttMunProdAnoAAnoCttVal

    FROM VWBNCCttMunProdAnoAAno

    ) AS SourceTable

    PIVOT

    (

    SUM(BNCCttMunProdAnoAAnoCttVal)

    FOR BNCCttMunProdAnoDt IN ([1999],[2000],[2001],[2002],[2003],[2004],[2005], [2006], [2007])

     

    ) AS PivotTable

    CREATE VIEW VWBNCCttMunProdAnoAAnoPvt

    AS

    SELECT Q.BNCCttTipIDProdAnoAAno,

    Q.BNCCttMunIDProdAnoAAno,

    Q.BNCCttMunProd1999Qtd,

    V.BNCCttMunProd1999CttVal,

    E.BNCCttMunProd1999EmprestVal,

    Q.BNCCttMunProd2000Qtd,

    V.BNCCttMunProd2000CttVal,

    E.BNCCttMunProd2000EmprestVal,

    Q.BNCCttMunProd2001Qtd,

    V.BNCCttMunProd2001CttVal,

    E.BNCCttMunProd2001EmprestVal,

    Q.BNCCttMunProd2002Qtd,

    V.BNCCttMunProd2002CttVal,

    E.BNCCttMunProd2002EmprestVal,

    Q.BNCCttMunProd2003Qtd,

    V.BNCCttMunProd2003CttVal,

    E.BNCCttMunProd2003EmprestVal,

    Q.BNCCttMunProd2004Qtd,

    V.BNCCttMunProd2004CttVal,

    E.BNCCttMunProd2004EmprestVal,

    Q.BNCCttMunProd2005Qtd,

    V.BNCCttMunProd2005CttVal,

    E.BNCCttMunProd2005EmprestVal,

    Q.BNCCttMunProd2006Qtd,

    V.BNCCttMunProd2006CttVal,

    E.BNCCttMunProd2006EmprestVal,

    Q.BNCCttMunProd2007Qtd,

    V.BNCCttMunProd2007CttVal,

    E.BNCCttMunProd2007EmprestVal

    FROM VWBNCCttMunProdAnoAAnoPvtCttVal AS V INNER JOIN

    VWBNCCttMunProdAnoAAnoPvtQtd AS Q ON V.BNCCttMunIDProdAnoAAno = Q.BNCCttMunIDProdAnoAAno AND

    V.BNCCttTipIDProdAnoAAno = Q.BNCCttTipIDProdAnoAAno INNER JOIN

    VWBNCCttMunProdAnoAAnoPvtEmprestVal AS E ON Q.BNCCttTipIDProdAnoAAno = E.BNCCttTipIDProdAnoAAno AND

    Q.BNCCttMunIDProdAnoAAno = E.BNCCttMunIDProdAnoAAno

    segunda-feira, 7 de janeiro de 2008 22:28