Pivoting and Unpivoting Data in SQL

Pivoting and unpivoting are techniques in SQL that allow you to transform data between column-based and row-based formats. Pivoting helps to aggregate and display data in a more readable and concise way by converting rows into columns. On the other hand, unpivoting helps you reverse this process, turning columns into rows for further analysis. In this article, we will explore both techniques with examples.

Pivoting Data in SQL

Pivoting is the process of turning rows into columns. This technique is useful when you want to summarize data in a compact form, especially when dealing with aggregate data.

Example: Pivoting Sales Data

Suppose you have a table sales with the following data:

        sales
        +----------+--------+-------+--------+
        | month_id | region | sales | profit |
        +----------+--------+-------+--------+
        | 1        | East   | 500   | 100    |
        | 1        | West   | 600   | 120    |
        | 2        | East   | 700   | 140    |
        | 2        | West   | 800   | 160    |
        +----------+--------+-------+--------+

We want to pivot this data so that each region appears as a separate column, and the sales values for each month are shown in those columns. The following SQL query uses the PIVOT operator:

        SELECT month_id,
               [East] AS East_Sales,
               [West] AS West_Sales
        FROM (SELECT month_id, region, sales
              FROM sales) AS SourceTable
        PIVOT (SUM(sales) FOR region IN ([East], [West])) AS PivotTable;

In this query:

  • The subquery SELECT month_id, region, sales retrieves the data.
  • The PIVOT operator transforms rows into columns based on the region column.
  • The SUM(sales) function aggregates the sales data for each month and region.

The result of this pivot query will look like this:

        +----------+------------+------------+
        | month_id | East_Sales | West_Sales |
        +----------+------------+------------+
        | 1        | 500        | 600        |
        | 2        | 700        | 800        |
        +----------+------------+------------+

Unpivoting Data in SQL

Unpivoting is the reverse of pivoting; it converts columns into rows. This technique is helpful when you need to normalize data or prepare it for further analysis.

Example: Unpivoting Sales Data

Let’s say you have a table monthly_sales that contains data for East and West regions as columns, and you want to unpivot the data to make it more granular:

        monthly_sales
        +----------+------------+------------+
        | month_id | East_Sales | West_Sales |
        +----------+------------+------------+
        | 1        | 500        | 600        |
        | 2        | 700        | 800        |
        +----------+------------+------------+

To unpivot this data, you can use the UNPIVOT operator as follows:

        SELECT month_id, region, sales
        FROM (SELECT month_id, East_Sales, West_Sales
              FROM monthly_sales) AS SourceTable
        UNPIVOT (sales FOR region IN (East_Sales, West_Sales)) AS UnpivotTable;

In this query:

  • The subquery SELECT month_id, East_Sales, West_Sales retrieves the data.
  • The UNPIVOT operator turns the columns East_Sales and West_Sales into rows under the region column.
  • The sales column contains the values from East_Sales and West_Sales after unpivoting.

The result of the unpivot query will look like this:

        +----------+--------+-------+
        | month_id | region | sales |
        +----------+--------+-------+
        | 1        | East   | 500   |
        | 1        | West   | 600   |
        | 2        | East   | 700   |
        | 2        | West   | 800   |
        +----------+--------+-------+

When to Use Pivoting and Unpivoting

Pivoting and unpivoting are powerful techniques for reshaping data in SQL, but knowing when to use them is important:

  • Pivoting: Use pivoting when you want to summarize data in a more readable form, especially when working with aggregate data or when you want to show values across multiple categories (e.g., regions, product categories, etc.) in columns.
  • Unpivoting: Use unpivoting when you need to normalize data, typically when you want to transform a wide table into a long format for easier analysis or reporting.

Conclusion

Pivoting and unpivoting are essential techniques for reshaping data in SQL. Pivoting allows you to convert rows into columns for better summarization, while unpivoting helps transform columns into rows for more granular analysis. Understanding when and how to use these techniques will help you manipulate your data effectively and produce the desired results in your reports or analysis.




Advertisement