Este artigo fornecerá uma visão geral completa do uso dos operadores PIVOT e UNPIVOT no SQL Server. Os operadores PIVOT e UNPIVOT são semelhantes aos operadores relacionais que permitem transformando a expressão com valor de tabela em outra tabela . Ambos os operadores geram relatórios multidimensionais que ajudam a combinar e comparar rapidamente uma grande quantidade de dados.
Podemos usar o Operador PIVOT quando precisamos transformar expressões com valor de tabela. Ele divide o valores únicos de uma coluna em muitas colunas no resultado final. Isso também agregados os valores restantes da coluna necessários no resultado final. Operador UNPIVOT converte dados de colunas de uma expressão com valor de tabela em valores de coluna, que é o inverso de PIVOT.
Vamos entendê-lo com a ajuda do diagrama simples fornecido abaixo:
construtor em java
No lado esquerdo desta figura, podemos ver o conjunto de dados original , que possui três colunas: Ano, Região, e Vendas . A seguir, podemos ver a tabela PIVOT do lado direito, que é construída girando o Região (linhas) em Norte e Sul (colunas) . Depois de converter linhas em colunas, podemos fazer um agregado de valores da coluna Vendas para cada interseção entre as colunas e linhas da tabela PIVOT.
Vamos primeiro criar uma tabela chamada pivot_demo para demonstrar os operadores PIVOT e UNPIVOT. A instrução a seguir cria uma nova tabela em nosso banco de dados especificado:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
A seguir, insira alguns dados nesta tabela conforme abaixo:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Podemos verificar os dados usando a instrução SELECT. Obteremos a saída abaixo:
Operador PIVOT
Este operador é usado para girar expressões com valor de tabela. Foi introduzido pela primeira vez na versão SQL Server 2005. Ele converte dados de linhas em colunas. Ele divide os valores exclusivos de uma coluna em várias colunas e, em seguida, agrega os valores restantes da coluna necessários no resultado final.
Devemos seguir os seguintes passos para criar uma tabela PIVOT:
- Selecione o conjunto de dados base para dinamização.
- Crie resultados temporários com a ajuda de uma tabela derivada ou CTE (expressão de tabela comum).
- Faça uso do operador PIVOT.
Sintaxe
A sintaxe a seguir ilustra o uso do PIVOT no SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Se quebrarmos esse script, podemos ver que ele possui duas seções separadas. A primeira seção seleciona os dados da tabela principal e a segunda seção determina como a tabela PIVOT será construída. A segunda parte também contém algumas palavras-chave especiais, como SUM, FOR e IN. Vejamos o significado dessas palavras-chave no operador PIVOT.
SOMA
Este operador é usado para agregar os valores da coluna especificada a ser usada na tabela PIVOT. Devemos usá-lo com o operador PIVOT para obter as exibições de colunas agregadas para as seções de valores.
PARA palavra-chave
Esta palavra-chave é usada para a instrução da tabela PIVOT para instrua o operador PIVOT em qual coluna a função PIVOT deve ser aplicada. Basicamente, indica os nomes das colunas que serão transformadas de linhas em colunas.
Palavra-chave IN
Esta palavra-chave lista todos os valores únicos da coluna PIVOT para serem exibidas como colunas da tabela PIVOT.
Exemplo
Vamos entendê-lo com a ajuda de vários exemplos.
1. A instrução a seguir seleciona primeiro as colunas Ano, Norte e Sul como dados base para dinamização. Em seguida, crie um resultado temporário usando a tabela derivada e finalmente aplique o operador PIVOT para gerar a saída final. Esta saída também é ordenada no ano crescente.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
A execução desta instrução produzirá a saída abaixo. Aqui, podemos ver o soma calculada das vendas da região Norte e Sul correspondente aos valores do ano .
2. Este é outro exemplo onde vamos calcular a soma das vendas de cada Ano correspondente aos valores da região:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
A execução desta declaração irá produzir um erro porque não podemos especificar o valor numérico diretamente como um nome de coluna.
No entanto, o SQL Server nos permite evitar esse problema usando colchetes antes de cada valor inteiro. A instrução atualizada é mostrada no seguinte trecho de código:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Esta instrução foi executada com sucesso e exibe a soma calculada das vendas para cada ano correspondente aos valores da região:
3. O exemplo anterior de obtenção de uma tabela PIVOT é útil quando estamos cientes de todos os valores possíveis da coluna PIVOT. Mas suponha que no próximo ano o número de colunas aumente. Considerando o exemplo anterior, temos os anos de 2010, 2011 e 2012 como colunas PIVOT. No entanto, não há garantia de que estas colunas não serão alteradas no futuro. O que acontece se tivermos dados de 2013 ou 2014, ou talvez até mais? Nesses casos, precisaremos usar tabela PIVOT dinâmica consultas para corrigir esse problema.
A consulta da tabela PIVOT dinâmica encapsula todo o script PIVOT em um procedimento armazenado. Este procedimento fornecerá opções ajustáveis, permitindo-nos modificar nossos requisitos alterando alguns valores parametrizados.
Sree Ramanujan
O código SQL a seguir explica o funcionamento da tabela PIVOT dinâmica. Neste script, primeiro recuperamos todos os valores distintos da coluna PIVOT e, em seguida, escrevemos uma instrução SQL para execução com a consulta PIVOT em tempo de execução. Vamos ver a saída após executar este script:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
Neste script, criamos duas variáveis parametrizadas. Sua descrição é dada abaixo:
@PivotColumn : Esta variável receberá o nome da coluna da tabela original na qual a tabela PIVOT foi criada. Por exemplo , aqui, a coluna 'Região' exibe todas as regiões disponíveis nas colunas.
@PivotList : esta variável pegará a lista de colunas que queremos exibir como coluna de saída na tabela PIVOT.
Execução de procedimento armazenado dinâmico
Após a criação bem-sucedida do procedimento armazenado dinâmico, estamos prontos para executá-lo. A instrução a seguir é usada para chamar o procedimento armazenado dinâmico para exibir a tabela PIVOT em tempo de execução:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Aqui especificamos o nome da coluna ' Região ' como o primeiro parâmetro e a lista da coluna PIVOT como o segundo parâmetro. A execução do script exibirá a seguinte saída:
Agora podemos adicionar mais colunas no futuro em tempo de execução para exibir a tabela PIVOT, o que não é possível com os dois primeiros exemplos.
Operador UNPIVOT
É o método reverso do operador PIVOT no SQL Server. É que este operador executa o operação oposta do PIVOT convertendo dados de colunas em linhas. O operador UNPIVOT também transforma a tabela PIVOT em uma tabela normal. Foi introduzido pela primeira vez na versão SQL Server 2005.
Sintaxe
A sintaxe a seguir ilustra o UNPIVOT no SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Exemplo
Vamos entender como UNPIVOT a operação PIVOT com exemplos. Primeiro criaremos um mesa original e mesa PIVOT e então aplicou o operador UNPIVOT nesta tabela.
O trecho de código a seguir declara primeiro uma variável de tabela temporária @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
A seguir, inseriremos valores nesta tabela conforme abaixo:
exemplo de dados json
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Agora, podemos realizar a operação UNPIVOT usando a instrução abaixo:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
A execução do trecho de código retornará a seguinte saída:
O trecho de código abaixo é outro exemplo para executar primeiro a operação PIVOT e depois a operação UNPIVOT na mesma tabela em uma única consulta:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
A execução do trecho de código exibirá a mesma saída:
NOTA: O processo UNPIVOT é uma operação inversa do procedimento PIVOT, mas não é uma reversão exata. Como as linhas foram mescladas quando o PIVOT calcula a agregação e combina muitas linhas em uma única linha no resultado, a operação UNPIVOT não pode tornar a tabela semelhante à original. No entanto, se o operador PIVOT não mesclar muitas linhas em uma única linha, o operador UNPIVOT poderá obter a tabela original da saída PIVOT.
Conclusão
Este artigo fornecerá uma visão geral completa dos operadores PIVOT e UNPIVOT no SQL Server e converterá uma expressão de tabela em outra. Nunca se deve esquecer que UNPIVOT é a operação inversa do PIVOT, mas não é o inverso exato do resultado do PIVOT.