FROM – Usando PIVOT e UNPIVOT

• 14/10/2019
• 5 minutos para ler
• • V
  • c
  • M
  • i
  • M
  • +7

Aplica-se a: SQL Server (todas as versões com suporte) Banco de dados SQL do Azure Instância gerenciada do Azure SQL Azure Synapse Analytics Data Warehouse paralelo

Você pode usar os operadores relacionais PIVOT e UNPIVOT para alterar uma expressão com valor de tabela em outra tabela. PIVOT gira uma expressão com valor de tabela, transformando os valores exclusivos de uma coluna na expressão em várias colunas na saída. E PIVOT executa agregações onde elas “são necessárias em quaisquer valores de coluna restantes que são desejados na saída final. UNPIVOT realiza a operação oposta para PIVOT girando colunas de uma expressão com valor de tabela em valores de coluna.

A sintaxe para PIVOT fornece é mais simples e mais legível do que a sintaxe que poderia ser especificado em uma série complexa de SELECT...CASE instruções. Para obter uma descrição completa da sintaxe de PIVOT, consulte FROM (Transact-SQL).

Sintaxe

A sintaxe a seguir resume como usar o operador PIVOT.

Observações

Os identificadores de coluna na cláusula UNPIVOT seguem o agrupamento do catálogo. Para Banco de Dados SQL, o agrupamento é sempre SQL_Latin1_General_CP1_CI_AS. Para SQL Server parcialmente contido bancos de dados, o agrupamento é sempre Latin1_General_100_CI_AS_KS_WS_SC. Se a coluna for combin ed com outras colunas, uma cláusula de agrupamento (COLLATE DATABASE_DEFAULT) é necessária para evitar conflitos.

Exemplo básico de PIVOT

O exemplo de código a seguir produz uma tabela de duas colunas com quatro linhas.

Aqui está o conjunto de resultados.

Nenhum produto é definido com três DaysToManufacture.

O código a seguir exibe o mesmo resultado, dinamizado de forma que DaysToManufacture os valores tornam-se os títulos das colunas. Uma coluna é fornecida por três dias, mesmo que os resultados sejam NULL.

Aqui está o conjunto de resultados .

Exemplo complexo de PIVOT

Um cenário comum em que PIVOT pode ser útil é quando você deseja gerar relatórios de tabulação cruzada para fornecer um resumo dos dados. Por exemplo, suponha que você queira consultar a tabela PurchaseOrderHeader no banco de dados de amostra AdventureWorks2014 para determinar o número de pedidos de compra feitos por certos funcionários. A consulta a seguir fornece este relatório, ordenado por fornecedor.

Aqui está um conjunto de resultados parcial.

Os resultados retornados por esta subseleção declaração são dinamizados na coluna EmployeeID.

Os valores exclusivos retornados pelo EmployeeID coluna tornam-se campos no conjunto de resultados final. Como tal, há “uma coluna para cada número EmployeeID especificado na cláusula dinâmica: neste caso, funcionários 250, 251, 256, 257 e 260. div id = “c36dcf6b3b”> coluna serve como a coluna de valor, contra a qual as colunas retornadas na saída final, que são chamadas de colunas de agrupamento, são agrupadas. Nesse caso, as colunas de agrupamento são agregadas pelo COUNT função. Observe que uma mensagem de aviso é exibida, indicando que quaisquer valores nulos que aparecem na PurchaseOrderID coluna não foram “considerados ao calcular o COUNT para cada funcionário.

Exemplo UNPIVOT

UNPIVOT realiza quase a operação reversa de PIVOT, girando as colunas em linhas. Suponha que a tabela produzida no exemplo anterior seja armazenada no banco de dados como pvt, e você deseja girar os identificadores de coluna Emp1, Emp2, Emp3, Emp4 e Emp5 em valores de linha que correspondem a um fornecedor específico. Como tal, você deve identificar duas colunas adicionais.A coluna que conterá os valores da coluna que você “rotaciona novamente (Emp1, Emp2, …) será chamada de Employee, e a coluna que conterá os valores que existem atualmente nas colunas que estão sendo giradas será chamada de Orders. Essas colunas correspondem a pivot_column e value_column , respectivamente, na definição Transact-SQL. Aqui está a consulta.

Aqui está um conjunto de resultados parcial.

Observe que UNPIVOT não é o reverso exato de PIVOT. PIVOT realiza uma agregação e mescla várias linhas possíveis em uma única linha na saída. UNPIVOT não reproduz o resultado da expressão com valor de tabela original porque as linhas foram mescladas. Além disso, os valores nulos na entrada de UNPIVOT desaparecem na saída. Quando os valores desaparecem, isso mostra que pode ter havido valores nulos originais na entrada antes da operação PIVOT.

O Sales.vSalesPersonSalesByFiscalYears a visualização no banco de dados de exemplo AdventureWorks2012 usa PIVOT para retornar o total de vendas de cada vendedor, para cada ano fiscal. Para criar um script da visualização no SQL Server Management Studio , no Pesquisador de Objetos, localize a visualização na pasta Visualizações do banco de dados AdventureWorks2012. Clique com o botão direito do mouse no nome da visualização e selecione Visualização de script como. -SQL)
CASE (Transact-SQL)