In SQL Server, the PIVOT and UNPIVOT functions enable you to restructure your data in various manners. Imagine you possess data organized in rows and wish to convert that data into columns. In this case, PIVOT will be utilized. Conversely, if you plan to invert this action and transform columns back into rows, then UNPIVOT will be the appropriate choice.

In this article, we will explore the PIVOT and UNPIVOT functions along with their practical application. Additionally, we will examine situations where the use of these functions is necessary.

Contents Overview

• Definition of PIVOT in SQL
• Illustration of PIVOT
• Definition of UNPIVOT in SQL
• Illustration of UNPIVOT
• When to Apply PIVOT and UNPIVOT in SQL
• Advanced Examples of PIVOT and UNPIVOT
• Optimal Practices for PIVOT and UNPIVOT Usage in SQL
• Practical Use Cases of PIVOT and UNPIVOT
• Summary

Understanding PIVOT in SQL

The PIVOT function stands out as one of SQL’s most advantageous elements, facilitating the conversion of row data into columns. This can be especially useful when you need to format your data in a more readable manner, particularly with reporting and analysis. When managing extensive datasets within business intelligence, it allows for a more visually appealing summary of the information.

PIVOT Structure in SQL:

SELECT <columns>
FROM
( 
    SELECT <column1>, <column2>, <aggregation_column>
    FROM <table_name>
) AS SourceTable
PIVOT
(
    <aggregate_function>(<aggregation_column>)
    FOR <column_to_pivot> IN (<list_of_new_columns>)
) AS PivotTable;

• Identify the data from the table and label it as SourceTable.
• Implement the PIVOT function to achieve the following tasks:
  • Incorporate an aggregation function such as SUM or COUNT.
  • Transform distinct entries from one field into column headings.
• The end result is a PivotTable which shifts data from rows to columns.

PIVOT Example

Consider a table named SalesData that includes three columns: ProductID, Month, and SalesAmount. The objective here is to reorganize the data so instead of displaying the sales data by month in rows, we will generate separate columns for each month. This makes it simpler to evaluate the sales data in a more organized manner.

Table Creation:

CREATE TABLE SalesData (
    ProductID INT,
    Month NVARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

-- Inserting Sample Data

INSERT INTO SalesData (ProductID, Month, SalesAmount)

VALUES (1, 'January', 1000),

       (1, 'February', 1200),

       (1, 'March', 1500),

       (2, 'January', 800),

       (2, 'February', 900),

       (2, 'March', 1100);

Employing the PIVOT Function:

SELECT ProductID, [January], [February], [March]
FROM
(
    SELECT ProductID, Month, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Month IN ([January], [February], [March])
) AS PivotTable;

Clarification:

• The above command begins by selecting ProductID, Month, and SalesAmount from the SalesData table.
• Subsequently, the PIVOT function is applied to categorize sales by month, effectively transforming months like January, February, and March into distinct columns.
• The end outcome illustrates the aggregated sales for each product across the months.

PIVOT Result:

Understanding UNPIVOT in SQL

The UNPIVOT function operates contrary to PIVOT. It takes data that spans multiple columns and reverts it to rows. This functionality is useful when you wish to reorganize your data back to its original state after analysis, ensuring it can be effectively stored in a database.

UNPIVOT Structure in SQL:

SELECT <columns>
FROM
(
    SELECT <column1>, <column_to_unpivot1>, <column_to_unpivot2>, ...
    FROM <table_name>
) AS SourceTable
UNPIVOT
(
    <unpivot_column> FOR <new_column_name> IN (<list_of_columns_to_unpivot>)
) AS UnpivotTable;

The initial step assigns the table’s data to a new variable, SourceTable, for future reference.

Next, the UNPIVOT function is instructed to take specific columns and convert them back into rows, while also entering their values into a new column. The structure transitions from ‘SourceTable’ to ‘UnpivotTable’. The new table format is now in rows rather than in columns.

UNPIVOT Illustration

Having previously transformed the `SalesData` table by converting months into columns, we are now prepared to revert that transformation and restore the original setup.

“`html

data to its initial state, where monthly sales are represented as rows again for every product.

Implementing the Unpivot Operator:

SELECT ProductID, Month, SalesAmount
FROM
(
    SELECT ProductID, [January], [February], [March]
    FROM
    (
        SELECT ProductID, Month, SalesAmount
        FROM SalesData
    ) AS SourceTable
    PIVOT
    (
        SUM(SalesAmount)
        FOR Month IN ([January], [February], [March])
    ) AS PivotTable
) AS PivotedData
UNPIVOT
(
    SalesAmount FOR Month IN ([January], [February], [March])
) AS UnpivotedData;

Clarification:

Initially, we have applied the PIVOT technique in our first illustration. It gathers and consolidates the information by month. We have now utilized the UNPIVOT operator to revert the months into rows again. This will return my original dataset.

Unpivot Result:

When to Employ Pivot and Unpivot in SQL?

Utilize Pivot when:

• You are required to convert row data into columns for enhanced reporting or analysis.
• You are engaged with aggregate functions like SUM(), AVG(), COUNT(), and wish to summarize data.

Utilize Unpivot when:

• You need to standardize data or reformat columns back into rows for analysis.
• Your dataset contains aggregated columns, and you need to deconstruct them into distinct rows.

Advanced Pivot and Unpivot Illustrations

Example 3: Pivoting with Multiple Aggregations

Imagine you wish to display the total sales for each product on a monthly basis.

SELECT ProductID, January, February, March, 
       January, February, March
FROM
(
    SELECT ProductID, Month, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount) FOR Month IN ([January], [February], [March])
) AS PivotTable1

Clarification:

• The query above employs two PIVOT operators, one for total sales (SUM()) and another to compute average sales (AVG()).

Example 4: Unpivot with Multiple Columns

Consider you maintaining a table that illustrates quarterly sales data; you now wish to modify it to present information for each month instead.

SELECT ProductID, 
       SUM(CASE WHEN Month IN ('January', 'February', 'March') THEN SalesAmount ELSE 0 END) AS Q1,
       SUM(CASE WHEN Month IN ('April', 'May', 'June') THEN SalesAmount ELSE 0 END) AS Q2,
       SUM(CASE WHEN Month IN ('July', 'August', 'September') THEN SalesAmount ELSE 0 END) AS Q3,
       SUM(CASE WHEN Month IN ('October', 'November', 'December') THEN SalesAmount ELSE 0 END) AS Q4
INTO SalesDataQuarterly
FROM SalesData
GROUP BY ProductID;

SELECT ProductID, Quarter, SalesAmount
FROM SalesDataQuarterly
UNPIVOT
(
    SalesAmount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotedData;

Clarification:

• The above query utilizes the UNPIVOT operator to transform columns representing each quarter into rows.

Best Practices for Executing Pivot and Unpivot in SQL

When employing PIVOT or UNPIVOT, there are essential considerations to keep in mind.

1. Performance Considerations: When handling large datasets, pivoting and unpivoting should be approached with caution as they can negatively impact system performance. Utilize indexed views or temporary tables to improve performance.
2. Clarity: Extensive unpivot or pivot queries using aggregations should be simplified in subqueries for enhanced comprehension.
3. Error Management: When adopting a dynamic method for column names in PIVOT and UNPIVOT, be mindful of potential errors arising from these actions.
4. Data Types: It is crucial that the data types of the columns you are pivoting or unpivoting are compatible for smooth execution.

Practical Applications of PIVOT and UNPIVOT

1. Reporting & Dashboards: Numerous organizations find it beneficial to generate sales reports that detail monthly sales separately, facilitating performance monitoring and trend analysis over time. PIVOT plays a pivotal role in presenting data in a structured and organized format, making concepts easier to grasp and comparisons simpler.
2. ETL & Data Warehousing: Large tables in data warehouses, which can feature numerous columns, may sometimes be challenging to manage. Unpivoting significantly aids in normalizing data, simplifying storage, analysis, and integration into analytics systems.
3. Comparative Analysis: Pivoting streamlines the process of organizing and contrasting sales data from different years. This is critically important for analysts as it allows them to easily discern patterns, growth, and seasonal variations.

Conclusion

In SQL Server, the PIVOT and UNPIVOT operators assist in converting data from rows to columns and vice versa. These functionalities simplify reporting, data examination, and data summarization. By mastering these operators, you will be able to handle data transformations more efficiently, enhancing your SQL effectiveness effortlessly. Manipulate the provided examples and prepare to confidently implement these operators in your endeavors.

The post Pivot and Unpivot in SQL appeared first on Intellipaat Blog.

“`