Examples of Reverse PIVOT Table in SQL Server
To understand the PIVOT and UNPIVOT in a better way, we need a table on which we will perform various operation and queries. Let’s say the below is the original table. We can transpose the table on the column Year or some other and convert the rows to columns. Call this as EmployeeTable
Person |
Year |
Salary |
---|---|---|
Sam |
2022 |
10000 |
John |
2023 |
5000 |
Sam |
2023 |
15000 |
John |
2022 |
20000 |
PivotedTable
This way it becomes very efficient to analyse the data.
Example 1: Converting The Rows a of Above EmployeeTable to Columns by Referring to The Values in Year Column
Query:
-- In the following query we are converting the rows a of above EmployeeTable to columns by referring to the values in Year Column
SELECT Person,
[2022],
[2023]
FROM (
SELECT Person, Year, Salary
FROM EmployeeTable
) AS SourceTable
PIVOT (
SUM(Salary)
FOR Year IN (
[2022],
[2023]
)
) AS PivotTable;
Output:
Person | 2022 | 2023 |
---|---|---|
Sam | 10000 | 15000 |
John | 20000 | 5000 |
Explanation of Queries:
In the below Query:
SELECT Person, Year, Salary FROM EmployeeTable
This inner query selects three columns – Person
, Year
, and Salary
– from the EmployeeTable
. It serves as the source table for the subsequent PIVOT operation.
- Person: It Represents the individual’s name.
- Year: It Denotes the year associated with the salary.
- Salary: Indicates is the salary amount for a specific person in a given year.
In the PIVOT Operation:
PIVOT (
SUM(Salary)
FOR Year IN ([2022], [2023])
) AS PivotTable
The PIVOT operation is applied to the SourceTable
. Here’s a breakdown of the PIVOT parameters:
SUM(Salary)
: This specifies that the aggregation function used is the sum of salaries. The PIVOT will calculate the sum for each combination ofPerson
and transposedYear
columns.FOR Year
IN ([
2022
], [
2023
])
: Defines the pivot columns. The distinct values in theYear
column –[
2022
]
and[
2023
]
– become the new column headers in the transposed table.
The result is stored in a table alias named PivotTable.
In the
Outer Query:
SELECT Person, [2022], [2023]
FROM PivotTable;
The outer query selects columns from the resulting PivotTable
. The transposed Year
columns, [
2022
]
and [
2023
]
, become new columns in the output. Each row in the output represents a person, and their respective salaries for the years 2022 and 2023 are displayed as separate columns.
Example 2: Reverse Pivoting the Previously Pivoted Table to its Original Form
Let us consider the above Pivoted Table and apply UNPIVOT on it to reverse the effect.
Query:
-- In the following query we are reverse pivoting the Previously Pivoted table to its original form
SELECT Person, Year, Salary
FROM (
SELECT Person, [2022], [2023]
FROM
PivotedTable
) AS SourceTable
UNPIVOT (
Salary FOR Year IN ([2022], [2023])
) AS UnpivotedTable;
Output:
Person | Year | Salary |
---|---|---|
Sam | 2022 | 10000 |
John | 2022 | 20000 |
Sam | 2023 | 15000 |
John | 2023 | 5000 |
Explanation of Queries:
Let’s break down the provided query step by step:
In the Inner Query (SourceTable):
SELECT Person, [2022], [2023]
FROM PivotedTable
This inner query selects three columns – Person
, [
2022
]
, and [
2023
]
– from the PivotedTable
. It serves as the source table for the subsequent UNPIVOT operation.
- Person: Represents the individual’s name.
- [2022] and [2023]: Denote the transposed columns representing years with corresponding salary values.
In the UNPIVOT Operation:
UNPIVOT (
Salary FOR Year IN ([2022], [2023])
) AS UnpivotedTable
The UNPIVOT operation is applied to the SourceTable
. Here’s a breakdown of the UNPIVOT parameters:
Salary FOR Year IN ([2022], [2023])
: Specifies that theSalary
column is derived from the values in the transposedYear
columns[
2022
]
and[
2023
]
. This operation essentially reverses the previous PIVOT, converting the columns back into rows.
The result is stored in a table alias named UnpivotedTable
.
In the Outer Query:
SELECT Person, Year, Salary
FROM UnpivotedTable;
The outer query selects columns from the resulting UnpivotedTable
. The Year
column is reintroduced, and each row now represents a combination of a person, a year, and the corresponding salary.
This result table is the outcome of reversing the pivot operation performed earlier. It transforms the pivoted data back to its original form, with the Year
and Salary
columns. Each row represents the salary of a person in a specific year.
Example 3: Let us Try Reverse Pivoting the Above PivotedTable Example
Query:
-- Apply this Statement on above PivotedTable to get back the EmployeeTable
SELECT Person, Year, Salary
FROM (
SELECT Person, [2022], [2023]
FROM
PivotedTable
) AS SourceTable
UNPIVOT (
Salary FOR Year IN ([2022], [2023])
) AS UnpivotedTable;
Output:
Explanation: The Output shows how the PIVOT and UNPIVOT can help we to transpose the table and reverse pivot the same pivoted table. The same query is executed in SSMS that produces the above shown output.
Example 4: Apply PIVOT and Reverse Pivot to Multiple Columns.
To perform this example we have created a table called OriginalTable which consist of Age, Year, Salary, and Expenses as Columns.
OriginalTable:
Person |
Age |
Year |
Salary |
Expenses |
---|---|---|---|---|
Ramesh |
35 |
2022 |
5000 |
4000 |
Suresh |
35 |
2022 |
6000 |
5000 |
Ramesh |
40 |
2023 |
10000 |
7000 |
Suresh |
40 |
2023 |
15000 |
10000 |
Query:
-- In this query we are using PIVOT on the above OriginalTable
SELECT Person, [35], [40], [2022], [2023]
FROM
(SELECT
Person, Age, Year, Salary
FROM OriginalTable) AS SourceTable
PIVOT
(
SUM(Salary)
FOR Age IN ([35], [40])
) AS QuantityPivot
PIVOT
(
SUM(Expenses)
FOR Year IN ([2022], [2023])
) AS PivotedTable
Output:
Query for Reverse Pivot:
-- Unpivoting the above PivotedTable
SELECT Person, Age, Year, Salary
FROM
(SELECT
Person,[35], [40], [2022], [2023]
FROM PivotedTable) AS SourceTable
UNPIVOT
(
Salary
FOR Age IN ([35], [40])
) AS QuantityPivot
UNPIVOT
(
Expenses
FOR Year IN ([2022], [2023])
) AS PivotedTable
Output:
Explanation: The Output shows how the PIVOT and UNPIVOT can help we to transpose the table and reverse pivot the same pivoted table. Here, we are implementing the PIVOT and UNPIVOT over multiple columns which are Salary and Expenses. The same query is executed in SSMS that produces the above shown output.
Reverse PIVOT Table in SQL Server
In SQL Server, the PIVOT operation is a powerful feature that allows you to transform rows into columns, providing a convenient way to structure and analyze data. However, there are situations where we may need to reverse this operation, converting columns back into rows. This process is commonly known as “unpivoting” or “reverse PIVOT”. The UNPIVOT
operator in SQL Server performs the reversal of the PIVOT operation. In this article, we will learn bout PIVOT and UNPIVOT in detail along with its syntax, practical examples and so on.