PIVOT and UNPIVOT in SQL

Unpivot and Pivot in sql server are powerful operations that help us rearrange and summarize data for easier analysis. Imagine you have a long list of sales data that's hard to read because it's spread out across many rows. With Pivot, you can transform this list into a compact table, organizing sales by categories or dates, making it simpler to understand trends. On the other hand, if your data is too compressed and you want to explore details more deeply, Unpivot does the reverse.

Syntax for PIVOT

The Pivot in SQL server allows you to transform row-based data into column-based data, making it easier to read and analyze. Here's a simplified version of the syntax:

In this structure:

• [desired columns] are the ones you want to display in the final output.
• [source table] is the table from which you're pivoting data.
• [aggregation function] is used to aggregate data, such as SUM, AVG, etc.
• [column to be aggregated] is the column you're applying the aggregation function to.
• [column to be transformed into headers] specifies which column's unique values will become column headers in the result set.
• [list of column headers] defines the specific values from the column that you want to turn into column headers in the output.
• alias_for_pivoted_table is a temporary name given to the pivoted table for use in the query.

Example 1: Using PIVOT Operator

Let's create an easy-to-understand example to illustrate how to use the pivot in SQL server. Imagine we have a sales table (SalesData) that records the number of products sold across different months.

The query to create the SalesData table and inserting data is:

Given a simplified version of our SalesData table structure:

Query

We want to pivot this table to see the total units sold of each product for each month as separate columns.

Here's the query using the PIVOT operator:

Output

The output of this query will be a pivoted table where each row represents a product, and the columns show the total units sold for January and February, like so:

Applying PIVOT Operator

In this example, the pivot in SQL is used to transform the Month rows into columns. By specifying SUM(UnitsSold) as the aggregation function, we aggregate the total units sold for each product per month. The FOR Month IN ([January], [February]) part of the query defines which columns we want to create from the row values - in this case, separate columns for January and February sales figures. This transformation allows us to quickly assess each product's performance across the months specified, enhancing data readability and analytical insight.

Syntax for UNPIVOT

The UNPIVOT operation reverses the PIVOT operation's effect, turning columns back into rows. The basic syntax for an UNPIVOT looks like this:

In this syntax:

• [desired columns] are the columns you wish to include in your final result. These often include identifiers or other data that wasn't unpivoted.
• [source table] is the table from which you're starting, containing the data in a pivoted (wide) format.
• [value column] represents the new column that will hold the values previously spread out across multiple columns.
• [new column] is the column that will contain the names of the columns being converted back into rows.
• [columns to be transformed into rows] specifies the specific columns from the source table that you wish to convert into rows.
• alias_for_unpivoted_table provides a temporary name to the result of the UNPIVOT operation for use within your query.

Example 1: Using UNPIVOT Operator

To illustrate the UNPIVOT operator's functionality, let's reverse the earlier PIVOT operation on the SalesData example, transforming the pivoted column data back into rows. This process is particularly helpful when we need to convert aggregated or cross-tabulated data into a format suitable for detailed analysis or reporting.

Query

To UNPIVOT this data, transforming the monthly sales columns back into rows, we use the following query:

Output

Executing the UNPIVOT query returns the data to a normalized format, where each row represents the sales figures for a product in a given month, resembling the original SalesData table structure:

Applying UNPIVOT Operator

In this example, the UNPIVOT operator takes the columnar data from the pivoted table and distributes it back into rows. The FOR Month IN ([January], [February]) clause specifies which columns to convert into rows, and UnitsSold becomes the value column holding the data that was spread across the January and February columns. By doing so, we've returned the dataset to a format where each record represents a unique combination of ProductID, Month, and UnitsSold, facilitating detailed analysis and processing.

This reverse transformation is a powerful feature for data preparation and cleaning, ensuring data is in the most appropriate format for the task at hand.