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.”
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.
[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.
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.
( <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()..
Aqui será especificado os campos e os valores que seram rotacionados para que assim os registros se torne campos da consulta.
Apelido do campo gerado e ordenação das informações que serão retornadas na consulta.
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
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
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
(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>,
FROM (SELECT NomeAssunto, TipoOcorrencia, Quantidade FROM dbo.DadosPvt) AS P = FROM
(SUM(Quantidade) FOR TipoOcorrencia IN (Reclamacao, Solicitação, Sugestao, Elogio, Denuncia))AS Pvt = FOR
Sendo assim o resultado será…
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.
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
(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.
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)+ ']‘)
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)
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.