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

Question

 

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+

 

Answer 1

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

 

Answer 2

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+

Answer 3

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

Answer 4

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