Como Utilizar o Operador Pivot

Posted on março 25, 2012

9


O operador PIVOT realiza a mudança de um registro para um campo, fazendo com que seja possível transformar um registro em uma coluna no banco de dados.

 

No BOL temos a seguinte explicação: “PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.”

 

Nomenclatura

Aqui irei detalhar cada passo para que assim possamos entender melhor como esse recurso pode ser utilizado.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    …

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    … [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

<non-pivoted column>

 

Esse(s) são os campos nos quais ficaram sem mudança, campos fixos, não serão rotacionados pelas cláusula PIVOT.

 

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,  … [last pivoted column] AS <column name>

Aqui é selecionado os registros que serão transformados em campos.  Essas colunas na verdade são registros de um campo que será rotacionado.

 

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

Aqui será especificado todos os campos que serão utilizados na consulta e de qual tabela será especificado os campos que irão compor a consulta.

 

PIVOT

( <aggregation function>(<column being aggregated>)

 

Aqui será especificado o argumento de agregação, ou seja o campo que será computado utilizando funções como SUM(), COUNT(), AVG()..

 

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

… [last pivoted column])

) AS <alias for the pivot table>

Aqui será especificado os campos e os valores que seram rotacionados para que assim os registros se torne campos da consulta.

 

) AS <alias for the pivot table>

<optional ORDER BY clause>;

Apelido do campo gerado e ordenação das informações que serão retornadas na consulta.

 

Exemplo de Utilização

Dada a explicação, agora iremos realizar um exemplo para melhor entendimento. Supondo que temos a seguinte estrutura.

 

 CREATE TABLE dbo.DadosPvt

(

     ID INT IDENTITY(1,1) NOT NULL,

     NomeAssunto VARCHAR(50) NOT NULL,

     TipoOcorrencia VARCHAR(50) NOT NULL,

     Quantidade INT NOT NULL,

     DataRegistro DATETIME2 NOT NULL DEFAULT GETDATE()

)

 

 

INSERT INTO dbo.DadosPvt (NomeAssunto, TipoOcorrencia, Quantidade)

VALUES (‘Infraestrutura’,‘Reclamacao’,1),

        (‘Infraestrutura’,‘Sugestao’,1),

        (‘Infraestrutura’,‘Elogio’,2),

        (‘Suporte Técnico’,‘Solicitação’,2),

        (‘Suporte Técnico’,‘Denuncia’,5),

        (‘Desenvolvimento de Sistemas’,‘Solicitação’,1),

        (‘Desenvolvimento de Sistemas’,‘Sugestao’,2),

        (‘Desenvolvimento de Sistemas’,‘Reclamacao’,1)

 

go

SELECT * FROM dbo.DadosPvt

image

 

Desejamos realizar um agrupamento para saber a quantidade de Assuntos por um determinado Tipo de Ocorrencia, bem para isso podemos utilizar a seguinte expressão.

 

SELECT NomeAssunto, TipoOcorrencia, SUM(Quantidade) AS Quantidade

FROM dbo.DadosPvt

GROUP BY NomeAssunto, TipoOcorrencia

ORDER BY NomeAssunto

image

Agora sabemos a quantidade de Assuntos por um determinado tipo de ocorrencia, mas se repararmos, o  Nome do Assunto se duplica diversas vezes, isso porque temos vários tipos de ocorrencia para um assunto. Com isso gostaria de mostrar esses dados de uma forma mais elegante, contendo somente um tipo de assunto e que os tipos de ocorrencia pudessem ser um campo contendo sua respectiva quantidade, bem para isso temos o nosso operador PIVOT. Refazendo a consulta…..

 

SELECT NomeAssunto, Reclamacao, Solicitação, Sugestao, Elogio, Denuncia

FROM (SELECT NomeAssunto, TipoOcorrencia, Quantidade FROM dbo.DadosPvt) AS P

PIVOT

(SUM(Quantidade) FOR TipoOcorrencia IN (Reclamacao, Solicitação, Sugestao, Elogio, Denuncia)) AS Pvt

Ou seja…..

 

NomeAssunto = <non-pivoted column>

Reclamacao, Solicitação, Sugestao, Elogio, Denuncia = [first pivoted column] AS <column name>,

[second pivoted column] AS <column name>, … [last pivoted column] AS <column name>

FROM (SELECT NomeAssunto, TipoOcorrencia, Quantidade FROM dbo.DadosPvt) AS P = FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(SUM(Quantidade) FOR TipoOcorrencia IN (Reclamacao, Solicitação, Sugestao, Elogio, Denuncia)) AS Pvt = FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

… [last pivoted column])

) AS <alias for the pivot table>

Sendo assim o resultado será…

 

image

 

Pronto, agora temos somente um valor de assunto contendo seus respectivos Tipos de Ocorrencia com suas quantidades..Porem se agora desejarmos criar um novo tipo de ocorrencia.

 

INSERT INTO dbo.DadosPvt (NomeAssunto, TipoOcorrencia, Quantidade)

VALUES (‘Suporte Técnico’,‘Agradecimento’,10)

 

Esse registro não será retornado na consulta anterior, porque esse registro não é especificado como uma coluna na cláusula SELECT e IN da consulta, sendo assim ele não é dinâmico, para que esse novo registro fosse mostrado, teriamos que adicioná-lo utilizando….

 

SELECT NomeAssunto, Reclamacao, Solicitação, Sugestao, Elogio, Denuncia, Agradecimento

FROM (SELECT NomeAssunto, TipoOcorrencia, Quantidade FROM dbo.DadosPvt) AS P

PIVOT

(SUM(Quantidade) FOR TipoOcorrencia IN (Reclamacao, Solicitação, Sugestao, Elogio, Denuncia, Agradecimento)) AS Pvt

Isso torna o PIVOT não muito utilizado em muitos casos, porque a cada vez que fosse adicionado um novo tipo de ocorrencia, teriamos que adicioná-lo na consulta.

 

Dynamic PIVOT

 

Mas pensando um pouco, podemos realizar um PIVOT Dinâmico, assim conseguiriamos sempre saber a quantidade de tipos de ocorrencias a retornar nesse caso.

Uma boa solução para isso seria utilizar a seguinte solução….

 

DECLARE @ColunasPivot VARCHAR(8000)

DECLARE @GrupoPivot NVARCHAR(MAX)

 

SELECT  @ColunasPivot = COALESCE(@ColunasPivot + ‘,[‘ + CAST(TipoOcorrencia AS VARCHAR) + ‘]’, ‘[‘ + CAST(TipoOcorrencia AS VARCHAR)+ ‘]’)

FROM dbo.DadosPvt

GROUP BY TipoOcorrencia

 

SET @GrupoPivot = N’

SELECT *

FROM (SELECT NomeAssunto, TipoOcorrencia, Quantidade FROM dbo.DadosPvt) AS P PIVOT

(SUM(Quantidade) FOR TipoOcorrencia IN (‘ + @ColunasPivot + ‘)) AS Pvt

 

EXECUTE(@GrupoPivot)

 

image

 

Sendo assim, com esse modelo toda a vez que for especificado um novo tipo de ocorrencia, não será necessário especificá-lo como uma nova coluna na consulta.