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.

What is Data Transposing?

The process of converting rows to columns in the data table is called Data Transposing. This can be done with matrices, tables, or other data structures that help to reorganize and reshape data to better suit the desired format. It allows convenient handling of data and facilitates specific types of analyses where the arrangement of data in rows and columns matters.

PIVOT in SQL Server

PIVOT is the SQL SERVER Syntax which is used in Data Transposing. This operation is useful when we want to rotate the result set of a query to make it more readable or to analyze it in a different way.

Syntax:

SELECT . . .
FROM (
    SELECT . . .
    FROM <Table Name>
) AS <Some Alias>
PIVOT (
    <Column name or Aggregate>
    FOR <Transpose Column> IN (
        <Column Names...>
    )
) AS <Some Alias>;

Explanation of Syntax:

• . . . is the list of columns to be selected.
  
• <Transpose Column> is the column to be transposed.
  
• <Column name or Aggregate> is the column for values or some aggregate function.

Reverse PIVOT or UNPIVOT in SQL Server

Reversing the already pivoted table is called Reverse PIVOT which is done using UNPIVOT. For example, the above table can be brought to its original form using UNPIVOT operator.

Syntax:

SELECT . . .
FROM (
    SELECT . . . 
    FROM
        <Table Name>
) AS SourceTable
UNPIVOT (
    <Column Name> FOR <Pivoted Column> IN (<Pivoted Column Values...>)
) AS UnpivotedTable;

Explanation of Syntax:

1. <Column name> is the column that gets its values from the Pivoted columns.
   
2. <Pivoted Column> is the column on which the PIVOT works.
   
3. <Pivoted Column Values...> is the list of values of Columns which where pivoted.

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

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:

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 of Person and transposed Year columns.
  
• FOR Year IN ([2022], [2023]): Defines the pivot columns. The distinct values in the Year 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:

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 the Salary column is derived from the values in the transposed Year 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:

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.

Conclusion

This is the way we can manipulate Data and additionally we can use Reverse Pivot with other Aggregate function part from SUM() , combine with other SQL features like JOIN and sort, filter the columns. we can use PIVOT, UNPIVOT to transpose the data as required in SQL Server or any other SQL Database. This will eventually help us to analyze the data as required. After reading whole article now you have a decent knowledge about the PIVOT and UNPIVOT and you can easily perform operations and queries.

nikhilkalburgi

Follow

Improve

Article Tags :

• Geeks Premier League
• SQL Server
• Databases
• Geeks Premier League 2023