In this article, we will walk through the two main basic operators of SQL server namely PIVOT and UNPIVOT. Both these are termed to be inverse operations but they are not inversely identical as well.

Pivot in SQL Server

PIVOT is the process of converting the data from the row level to the column level.
All these data are represented in a table as the outcome. The PIVOT operator can be paired with aggregate operation as well.

Syntax for Pivot

SELECT ,
[first_pivoted_column] AS first_column_name,
[second_pivoted_column] AS second_column_name,
...
[last_pivoted_column] AS last_column_name
FROM
(SELECT query) AS alias_select_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_pivot_table
optional_ORDER BY_clause;

Pivot example

SELECT 'TotalSales' AS TotalSales, [1], [2], [3], [4]
FROM
(
SELECT amount, [CUSTOMER_ID]
FROM contracts
) AS PivotSource
PIVOT
(
SUM(amount)
FOR [CUSTOMER_ID] IN ([1], [2], [3], [4])
) AS PivotTable;

Unpivot in SQL Server

UNPIVOT operator's functionality is opposite to PIVOT functionality. Here, UNPIVOT converts data from the column level to the row level. If the user prefers to switch the column identifiers to row levels, UNPIVOT comes in handy.

Though UNPIVOT is termed to be the opposite operation, it is not an exact opposite or reverse operation. PIVOT operations perform aggregation and merging for rows.
UNPIVOT doesn't perform the exact reversal as rows are previously merged.

Unpivot example

SELECT * FROM
(
SELECT amount, [CUSTOMER_ID]
FROM contracts
) AS TableToPivot
PIVOT
(
SUM(amount)
FOR [CUSTOMER_ID] IN ([1], [2], [3], [4])
) AS pvt
UNPIVOT
(
TotalSales for [CUSTOMER_ID] IN ([1], [2], [3], [4])
) AS upvt;