Visualize a situation where the data wants to be displayed in a different orientation than the data is being stored, with the conditions of row and column layout. The process of transforming data from row-based to column-based is called pivot . The PIVOT and UNPIVOT operators in SQL Server help change the data orientation from column-oriented to row-oriented and vice versa. This is done by merging the values contained in a column into a list of distinct values and then projecting that list as a column header.

PIVOT . operator

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

Syntax:

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

To put it more simply, to use PIVOT we need 3 components in the syntax:

• In the FROM clause, the input column must always be provided, the PIVOT operator uses these columns to identify which columns should be used to group the data for the aggregation.
• The comma separator that appears in the feed data will be used as the column heading for the pivoted data.
• With an Aggregation function, such as SUM, used to perform calculations and grouping of records.

For example:

Considering the situation below, when you want to get the average cost of repair days in the cases of 1,2,3,4 days, if you use GROUP BY normally the syntax will be as follows:

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

This results in a missing DaysToManufacturing record of 3, because in the case of 3 COST prices cannot be averaged because there is no data. This issue can be handled through PIVOT as follows:

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;

Example 2:

Get the top 5 SalesYTD totals of the Sales.SalesTerritory table, as follows:

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

And because the data is small, it only shows the value of each area, so we can spread the data horizontally so that when querying the database, the database will return only one line, increasing the ability to read and analyze for the database. query person:

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;

A major challenge in writing queries using PIVOT is the requirement to provide a fixed list of spread elements for the PIVOT operator. It is not feasible or impractical to do this for a large number of spread elements. To overcome this, developers can use dynamic SQL. Dynamic SQL provides a solution to construct a string literal that is passed to SQL Server, interpreted as a command, and then executed by SQL Server.

UNPIVOT . operator

The UNPIVOT operator is understood as the opposite of PIVOT , meaning to rotate the axis from column to row. Unpivot will not store the original value, detailed data has been lost during aggregation when pivoting. UNPIVOT does not have the ability to allocate a value to return the original detail data. Instead of converting rows to columns, the result of unpivot converts columns to rows. SQL Server provides the UNPIVOT operator to convert data from pivoted tables to row-based orientation.

While unpivoting data, one or more columns defined as the source will be converted to rows. The data in these rows is spread or split from one to several rows, based on how many columns need unpivoted.

For 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