FROM – usando PIVOT e UNPIVOT

Artigo
08/13/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

A sintaxe para PIVOT é mais fácil e legível do que a sintaxe que poderia ser especificada em uma série complexa de SELECT...CASE instruções. Para obter uma descrição completa da sintaxe de PIVOT, consulte a cláusula FROM.

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

Sintaxe

Esta seção resume como usar o PIVOT operador and UNPIVOT .

Sintaxe para o PIVOT operador.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <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 become column headers>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]

Sintaxe para o UNPIVOT operador.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <output column for names of the pivot columns> [ AS <column name> ] , ]
    [ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
    ( <SELECT query that produces the data> )
    AS <alias for the source query>
UNPIVOT
(
    <new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]

Comentários

Os identificadores de coluna na cláusula UNPIVOT seguem a ordenação de catálogo.

Para o Banco de Dados SQL do Azure, a ordenação é sempre SQL_Latin1_General_CP1_CI_AS.
Para bancos de dados parcialmente independentes do SQL Server, a ordenação é sempre Latin1_General_100_CI_AS_KS_WS_SC.

Se a coluna for combinada com outras colunas, uma cláusula COLLATE (COLLATE DATABASE_DEFAULT) será necessária para evitar conflitos.

Nos pools do Microsoft Fabric e do Azure Synapse Analytics, as consultas com PIVOT o operador falharão se houver uma GROUP BY saída na coluna não dinâmica por PIVOT. Como solução alternativa, remova a coluna não dinâmica do GROUP BY. Os resultados da consulta são os mesmos, pois essa GROUP BY cláusula é uma duplicata.

Exemplo de PIVOT básico

O seguinte exemplo de código produz uma tabela de duas colunas que tem quatro linhas.

USE AdventureWorks2022;
GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Este é o conjunto de resultados.

DaysToManufacture  AverageCost
------------------ ------------
0                  5.0885
1                  223.88
2                  359.1082
4                  949.4105

Nenhum produto é definido com um valor de 3 para DaysToManufacture.

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

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
    [0], [1], [2], [3], [4]
FROM (
    SELECT DaysToManufacture,
        StandardCost
    FROM Production.Product
) AS SourceTable
PIVOT (
    AVG(StandardCost) FOR DaysToManufacture IN
    ([0], [1], [2], [3], [4])
) AS PivotTable;

Este é o conjunto de resultados.

CostSortedByProductionDays  0           1           2           3           4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost                 5.0885      223.88      359.1082    NULL        949.4105

Exemplo de PIVOT complexo

Um cenário comum em que PIVOT pode ser útil ocorre quando você deseja gerar relatórios de tabulação cruzada para fornecer um resumo dos dados. Por exemplo, suponha que você deseje consultar a tabela PurchaseOrderHeader no banco de dados de exemplo AdventureWorks2022 para determinar o número de ordens de compra colocadas por alguns funcionários. A consulta a seguir fornece esse relatório, ordenado por fornecedor.

USE AdventureWorks2022;
GO

SELECT VendorID,
    [250] AS Emp1,
    [251] AS Emp2,
    [256] AS Emp3,
    [257] AS Emp4,
    [260] AS Emp5
FROM
(
    SELECT PurchaseOrderID,
    EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN ([250], [251], [256], [257], [260])
) AS pvt
ORDER BY pvt.VendorID;

Este é um conjunto de resultados parcial.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492        2           5           4           4           4
1494        2           5           4           5           4
1496        2           4           4           5           5
1498        2           5           4           4           4
1500        3           4           4           5           4

Os resultados retornados por essa instrução subselecionar são dinamizados na coluna EmployeeID.

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

Os valores exclusivos retornados pela coluna EmployeeID tornam-se campos no conjunto de resultados final. Dessa forma, há uma coluna para cada EmployeeID número especificado na cláusula dinâmica, que são funcionários 250, 251, 256, 257e 260 neste exemplo. A coluna PurchaseOrderID 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. Neste caso, as colunas de agrupamento são agregadas pela função COUNT. É exibida uma mensagem de aviso que indica que todos os PurchaseOrderID valores nulos que aparecem na coluna não foram considerados ao calcular o COUNT para cada funcionário.

Importante

Quando as funções de agregação são usadas com PIVOT, a presença de quaisquer valores nulos na coluna de valor não é considerada ao calcular uma agregação.

Exemplo de UNPIVOT

UNPIVOT executa praticamente a operação inversa de PIVOT, transformando colunas em linhas. Suponha que a tabela produzida no exemplo anterior seja armazenada no banco de dados como pvt e que você deseje girar os identificadores de coluna Emp1, Emp2, Emp3, Emp4 e Emp5 em valores de linhas que correspondam a um fornecedor específico. Dessa forma, você deve identificar duas colunas extras.

A coluna que contém os valores de coluna que você está girando (Emp1, Emp2, e assim por diante) é chamada Employeede , e a coluna que contém os valores que existem atualmente nas colunas que estão sendo giradas é chamada Ordersde . Essas colunas correspondem a pivot_column e value_column, respectivamente, na definição de Transact-SQL. Aqui está a consulta.

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (
    VendorID INT,
    Emp1 INT,
    Emp2 INT,
    Emp3 INT,
    Emp4 INT,
    Emp5 INT);
GO

INSERT INTO pvt
VALUES (1, 4, 3, 5, 4, 4);

INSERT INTO pvt
VALUES (2, 4, 1, 5, 5, 5);

INSERT INTO pvt
VALUES (3, 4, 3, 5, 4, 4);

INSERT INTO pvt
VALUES (4, 4, 2, 5, 5, 4);

INSERT INTO pvt
VALUES (5, 5, 1, 5, 5, 5);
GO

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM (
    SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt
) p
UNPIVOT
(
    Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt;
GO

Este é um conjunto de resultados parcial.

VendorID    Employee    Orders
----------- ----------- ------
1            Emp1       4
1            Emp2       3
1            Emp3       5
1            Emp4       4
1            Emp5       4
2            Emp1       4
2            Emp2       1
2            Emp3       5
2            Emp4       5
2            Emp5       5

UNPIVOT não é exatamente o inverso de PIVOT. PIVOT executa uma agregação e mescla possíveis linhas múltiplas em uma única linha na saída. UNPIVOT não reproduz o resultado da expressão com valor de tabela original, pois as linhas foram mescladas. Além disso, NULL os valores na entrada de UNPIVOT desaparecem na saída. Quando os valores desaparecem, isso mostra que pode ter havido valores originais NULL na entrada antes da PIVOT operação.

A exibição Sales.vSalesPersonSalesByFiscalYears no banco de dados de exemplo AdventureWorks2022 usa PIVOT para retornar o total de vendas de cada vendedor, para cada ano fiscal. Para gerar um script da exibição no SQL Server Management Studio, no Pesquisador de Objetos, localize a exibição na pasta Exibições do banco de dados AdventureWorks2022. Clique com o botão direito do mouse no nome da exibição e selecione Gerar Script da Exibição como.

Partilhar via

FROM – usando PIVOT e UNPIVOT

Sintaxe

Comentários

Exemplo de PIVOT básico

Exemplo de PIVOT complexo

Exemplo de UNPIVOT

Comentários

Recursos adicionais

Partilhar via

FROM – usando PIVOT e UNPIVOT

Sintaxe

Comentários

Exemplo de PIVOT básico

Exemplo de PIVOT complexo

Exemplo de UNPIVOT

Conteúdo relacionado

Comentários

Recursos adicionais