Visualize uma situação em que os dados desejam ser exibidos em uma orientação diferente da que os dados estão sendo armazenados, com as condições de layout de linha e coluna. O processo de transformação de dados baseados em linha para baseados em coluna é chamado de pivô . Os operadores PIVOT e UNPIVOT no SQL Server ajudam a alterar a orientação dos dados de orientados a colunas para orientados a linhas e vice-versa. Isso é feito mesclando os valores contidos em uma coluna em uma lista de valores distintos e, em seguida, projetando essa lista como um cabeçalho de coluna.

PIVOT. operador

Documentos: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

Sintaxe:

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>;

Simplificando, para usar PIVOT precisamos de 3 componentes na sintaxe:

• Na cláusula FROM, a coluna de entrada deve sempre ser fornecida, o operador PIVOT utiliza essas colunas para identificar quais colunas devem ser utilizadas para agrupar os dados para a agregação.
• O separador de vírgulas que aparece nos dados do feed será usado como o cabeçalho da coluna para os dados dinâmicos.
• Com uma função de Agregação, como SUM, utilizada para realizar cálculos e agrupamento de registros.

Por exemplo:

Considerando a situação abaixo, quando você deseja obter o custo médio de dias de reparo nos casos de 1,2,3,4 dias, se você usar GROUP BY normalmente a sintaxe será a seguinte:

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

Isso resulta em um registro ausente de DaysToManufacturing de 3, porque no caso de 3 os preços COST não podem ser calculados porque não há dados. Este problema pode ser tratado através do PIVOT da seguinte forma:

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [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;

Exemplo 2:

Obtenha os 5 principais totais SalesYTD da tabela Sales.SalesTerritory, da seguinte forma:

SELECT TOP 5 SUM(SalesYTD) AS TotalSalesYTD, Name FROM Sales.SalesTerritory GROUP BY Name

E como os dados são pequenos, ele mostra apenas o valor de cada área, então podemos distribuir os dados horizontalmente para que ao consultar o banco de dados, o banco de dados retorne apenas uma linha, aumentando a capacidade de leitura e análise para o banco de dados. pessoa:

SELECT TOP 5 'TotalSalesYTD' AS GrandTotal, [NorthWest],[NorthEast],[Central],[Southwest],[Southeast] FROM (SELECT TOP 5 Name,SalesYTD FROM Sales.SalesTerritory) AS SourceTable PIVOT (SUM(SalesYTD) FOR Name IN ([NorthWest],[NorthEast],[Central],[Southwest],[Southeast])) AS PivotTable;

Um grande desafio ao escrever consultas usando PIVOT é o requisito de fornecer uma lista fixa de elementos de propagação para o operador PIVOT. Não é viável ou impraticável fazer isso para um grande número de elementos de espalhamento. Para superar isso, os desenvolvedores podem usar SQL dinâmico. O SQL dinâmico fornece uma solução para construir um literal de cadeia de caracteres que é passado para o SQL Server, interpretado como um comando e executado pelo SQL Server.

operador UNPIVOT .

O operador UNPIVOT é entendido como o oposto de PIVOT , significando girar o eixo de coluna para linha. Unpivot não armazenará o valor original, dados detalhados foram perdidos durante a agregação ao girar. UNPIVOT não tem a capacidade de alocar um valor para retornar os dados de detalhes originais. Em vez de converter linhas em colunas, o resultado de unpivot converte colunas em linhas. O SQL Server fornece o operador UNPIVOT para converter dados de tabelas dinâmicas em orientação baseada em linha.

Ao não dinamizar os dados, uma ou mais colunas definidas como a origem serão convertidas em linhas. Os dados nessas linhas são distribuídos ou divididos de uma para várias linhas, com base em quantas colunas precisam ser não dinâmicas.

Por exemplo:

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