Written by • April 20, 2018• 17:06• Database development, Statements

Unleash the Power of SQL Pivot and Unpivot: A Complete Guide to Data Transposing Techniques

HomeDatabase development, StatementsUnleash the Power of SQL Pivot and Unpivot: A Complete Guide to Data Transposing Techniques

Data manipulation is an essential skill for anyone working with SQL databases. It allows you to transform and analyze data, making it more accessible and meaningful for various purposes, such as reporting or decision-making. Among the many techniques available in SQL, Pivot and Unpivot operations, as well as data transposing, play a significant role in restructuring data for easier consumption.

In this comprehensive guide, we will delve into the concepts of SQL Pivot, Unpivot and data transposing, exploring their use cases and providing you with practical examples. We will also discuss alternative methods for transposing data without the PIVOT operator, helping you to master these essential data manipulation techniques and make your data more versatile for analysis.

The article is structured as follows:

  1. SQL Pivot: Converting Rows to Columns
  2. SQL Unpivot: Converting Columns to Rows
  3. Transposing Data without the PIVOT Operator
  4. Combining Pivot and Unpivot Operations
  5. Create a Pivot Table with Query Builder
  6. Conclusion

By the end of this guide, you will have a solid understanding of SQL Pivot, Unpivot, and data transposing techniques, enabling you to apply them effectively in your own data analysis tasks.

SQL Pivot: Converting Rows to Columns

In various situations, you might need to reorganize your data for better analysis or presentation. One such technique is pivoting, which involves converting rows to columns in a table. SQL PIVOT operators allow you to perform this transformation, making data analysis and visualization more accessible.

Syntax and key components

The basic syntax for the PIVOT operator is as follows:

SELECT NonPivotedColumnName,
       [FirstPivotedColumnName] AS ColumnAlias,
       [SecondPivotedColumnName] AS ColumnAlias,
       ...
       [LastPivotedColumnName] AS ColumnAlias
FROM (
    SELECT query_that_produces_data
) AS SourceTableAlias
PIVOT (
    AggregationFunction(ColumnName)
    FOR ColumnNameWithUniqueValues IN (
        [FirstPivotedColumnName],
        [SecondPivotedColumnName],
        ...
        [LastPivotedColumnName]
    )
) AS PivotTableAlias;
- Non-pivoted columns
Non-pivoted columns are those that you want to keep as-is in the result set. These columns will not be affected by the PIVOT operation.
- Pivoted columns
Pivoted columns are the new columns created by the PIVOT operation. They are derived from the unique values of a single column in the source data.
- Aggregation functions
The PIVOT operator requires an aggregation function (such as SUM, COUNT, AVG, MIN, or MAX) to perform calculations on the remaining column values. This function is applied to the data being pivoted.
- Source query
The source query is a SELECT statement that retrieves the data for the PIVOT operation. This query should return the columns you want to pivot and any additional columns you want to include in the final result set.

Examples

Suppose we have a table named ‘ProductSales’ with the following columns and data:

ProductYearQuantity
Laptop2020100
Laptop2021150
Smartphone2020200
Smartphone2021250
Tablet202050
Tablet202180

Now, we want to pivot the table based on the ‘Year’ column, so that we have a separate column for each year’s sales quantities. Here’s the SQL query using the PIVOT operator:

SELECT Product,
       [2020] AS Sales_2020,
       [2021] AS Sales_2021
FROM (
    SELECT Product, Year, Quantity
    FROM ProductSales
) AS SourceTable
PIVOT (
    SUM(Quantity)
    FOR Year IN (
        [2020],
        [2021]
    )
) AS PivotTable;

The output of the query would be:

ProductSales_2020Sales_2021
Laptop100150
Smartphone200250
Tablet5080

As you can see, the PIVOT operator has transformed the ‘Year’ column values into separate columns, making it easier to analyze sales quantities across different products and years.

Additional examples and use cases

In addition to the simple example above, the SQL PIVOT operator can be used in more complex scenarios, such as:

Pivoting multiple columns

You can pivot more than one column by including additional aggregation functions and pivoted columns in your query. This allows you to analyze multiple attributes at once. Let’s assume you have a table called Sales with the following data:

ProductRegionQuantityRevenue
LaptopEast1015000
LaptopWest1218000
TabletEast159000
TabletWest2012000
MonitorEast84000
MonitorWest52500

Now, you want to create a pivot table that shows the total quantity and revenue for each product by region. Here’s how to do it using the PIVOT operator:

SELECT 
    Product,
    [East] AS East_Quantity,
    [West] AS West_Quantity,
    [East_Revenue] AS East_Revenue,
    [West_Revenue] AS West_Revenue
FROM
    (SELECT 
        Product,
        Region,
        Quantity,
        Revenue,
        Region + '_Revenue' AS Revenue_Region
     FROM Sales) AS SourceTable
PIVOT
    (
        SUM(Quantity)
        FOR Region IN ([East], [West])
    ) AS QuantityPivot
PIVOT
    (
        SUM(Revenue)
        FOR Revenue_Region IN ([East_Revenue], [West_Revenue])
    ) AS RevenuePivot

This query will produce the following result:

ProductEast_QuantityWest_QuantityEast_RevenueWest_Revenue
Laptop10121500018000
Tablet1520900012000
Monitor8540002500

In this example, we have pivoted the Quantity and Revenue columns using the SUM aggregation function. We’ve also created an additional derived column Revenue_Region to differentiate between the revenue columns for each region. Finally, we used two separate PIVOT operators to pivot both the quantity and revenue data in a single query.

Using different aggregation functions

Depending on your data analysis requirements, you can use various aggregation functions like COUNT, MIN, MAX, or AVG to perform calculations on the data being pivoted.

First, let’s create the SupportTickets table with the additional ProcessingTime column:

CREATE TABLE SupportTickets (
    TicketID INT,
    Product NVARCHAR(50),
    Status NVARCHAR(50),
    ProcessingTime INT
);
INSERT INTO SupportTickets (TicketID, Product, Status, ProcessingTime)
VALUES (1, 'Laptop', 'Open', 10),
       (2, 'Laptop', 'Closed', 30),
       (3, 'Tablet', 'Open', 15),
       (4, 'Tablet', 'Closed', 25),
       (5, 'Tablet', 'Closed', 20),
       (6, 'Monitor', 'Open', 5);

Here’s the result for the SupportTickets table with the additional ProcessingTime column after inserting the sample data:

TicketIDProductStatusProcessingTime
1LaptopOpen10
2LaptopClosed30
3TabletOpen15
4TabletClosed25
5TabletClosed20
6MonitorOpen5

Now, we will create a pivot table that shows the MIN, MAX, and AVG of ProcessingTime for each product based on their status using the PIVOT operator:

SELECT
    Product,
    [OpenMin] AS OpenMinTime,
    [ClosedMin] AS ClosedMinTime,
    [OpenMax] AS OpenMaxTime,
    [ClosedMax] AS ClosedMaxTime,
    [OpenAvg] AS OpenAvgTime,
    [ClosedAvg] AS ClosedAvgTime
FROM (
    SELECT
        Product,
        Status,
        ProcessingTime
    FROM
        SupportTickets
) AS SourceData
PIVOT (
    MIN(ProcessingTime) FOR Status IN ([OpenMin], [ClosedMin])
) AS MinPivot
PIVOT (
    MAX(ProcessingTime) FOR Status IN ([OpenMax], [ClosedMax])
) AS MaxPivot
PIVOT (
    AVG(ProcessingTime) FOR Status IN ([OpenAvg], [ClosedAvg])
) AS AvgPivot;

This query will produce the following result:

ProductOpenMinTimeClosedMinTimeOpenMaxTimeClosedMaxTimeOpenAvgTimeClosedAvgTime
Laptop1030103010.0030.00
Tablet1520152015.0022.50
Monitor5NULL5NULL5.00NULL

In this example, we used the PIVOT operator with the MIN, MAX, and AVG aggregation functions to display the minimum, maximum, and average ProcessingTime for each product based on their status (Open or Closed). The pivot table shows these values separately for each status and product.

Pivoting on non-numeric data

While the example above focused on numeric data, you can also pivot on non-numeric data using aggregation functions like COUNT or custom aggregate functions if supported by your database system. First, let’s create the SupportTickets table with the same data:

CREATE TABLE SupportTickets (
    TicketID INT,
    Product NVARCHAR(50),
    Status NVARCHAR(50)
);
INSERT INTO SupportTickets (TicketID, Product, Status)
VALUES (1, 'Laptop', 'Open'),
       (2, 'Laptop', 'Closed'),
       (3, 'Tablet', 'Open'),
       (4, 'Tablet', 'Closed'),
       (5, 'Tablet', 'Closed'),
       (6, 'Monitor', 'Open');

Now, you want to create a pivot table that shows the count of tickets for each product and their status. Here’s how to do it using the PIVOT operator:

SELECT
    Product,
    [Open] AS OpenTickets,
    [Closed] AS ClosedTickets
FROM
    (SELECT Product, Status, TicketID
     FROM SupportTickets) AS SourceTable
PIVOT
    (
        COUNT(TicketID)
        FOR Status IN ([Open], [Closed])
    ) AS PivotTable;

This query will produce the following result:

ProductOpenTicketsClosedTickets
Laptop11
Tablet12
Monitor10

In this example, we used the PIVOT operator with the COUNT function to count the number of tickets for each product based on their status (Open or Closed). The pivot table displays the count of open and closed tickets for each product, even though the Status column contains non-numeric data.

Filtering and sorting pivoted data

You can apply WHERE and ORDER BY clauses to the pivoted result set to filter and sort the data based on specific conditions. 

Here’s an example that demonstrates the use of the PIVOT operator along with the WHERE and ORDER BY clauses to filter and sort the pivoted data. Suppose we have the following SalesData table:

SalespersonProductQuantity
AliceLaptop5
AliceTablet3
BobLaptop7
BobMonitor2
CarolLaptop4
CarolTablet6

We want to retrieve the total quantity of each product sold by each salesperson, but only include salespersons who have sold at least 5 units of a product. Additionally, we want to sort the result by the salesperson’s name. Here’s the SQL query that achieves this:

WITH SalesPivot AS (
    SELECT
        Salesperson,
        [Laptop] AS LaptopSales,
        [Tablet] AS TabletSales,
        [Monitor] AS MonitorSales
    FROM (
        SELECT
            Salesperson,
            Product,
            Quantity
        FROM
            SalesData
    ) AS SourceData
    PIVOT (
        SUM(Quantity) FOR Product IN ([Laptop], [Tablet], [Monitor])
    ) AS PivotTable
)

SELECT
    *
FROM
    SalesPivot
WHERE
    LaptopSales >= 5
    OR TabletSales >= 5
    OR MonitorSales >= 5
ORDER BY
    Salesperson;

Result:

SalespersonLaptopSalesTabletSalesMonitorSales
Alice53NULL
Bob7NULL2
Carol46NULL

In this example, we first used a common table expression (CTE) to create the pivoted data with the PIVOT operator. Then, we applied the WHERE clause to filter out the salespersons who sold at least 5 units of a product, and finally, we used the ORDER BY clause to sort the result by the salesperson’s name.

Combining PIVOT with other SQL features

You can use PIVOT in conjunction with other SQL features like JOINs, subqueries, and common table expressions (CTEs) to create more complex data transformations and analysis. Suppose we have the following tables:

Orders table:

OrderIDCustomerIDOrderDate
112022-06-01
222022-06-02
312022-06-03
432022-06-04

OrderDetails table:

OrderIDProductQuantityUnitPrice
1Laptop21000
1Tablet1500
2Monitor3200
3Laptop11000
3Tablet2500
4Monitor1200

Customers table:

CustomerIDCustomerName
1Alice
2Bob
3Carol

Our goal is to create a summary report of the total sales amount for each product per customer. Here’s the SQL query that combines PIVOT with JOINs, a subquery, and a CTE to achieve this:

WITH SalesData AS (
    SELECT
        c.CustomerName,
        od.Product,
        od.Quantity * od.UnitPrice AS SalesAmount
    FROM
        Orders o
        JOIN OrderDetails od ON o.OrderID = od.OrderID
        JOIN Customers c ON o.CustomerID = c.CustomerID
)

SELECT
    CustomerName,
    [Laptop] AS LaptopSales,
    [Tablet] AS TabletSales,
    [Monitor] AS MonitorSales
FROM (
    SELECT
        CustomerName,
        Product,
        SalesAmount
    FROM
        SalesData
) AS SourceData
PIVOT (
    SUM(SalesAmount) FOR Product IN ([Laptop], [Tablet], [Monitor])
) AS PivotTable;

Result:

CustomerNameLaptopSalesTabletSalesMonitorSales
Alice25001000NULL
BobNULLNULL600
CarolNULLNULL200

In this example, we first used a CTE named SalesData to combine the relevant data from the Orders, OrderDetails, and Customers tables using JOINs. Then, we applied the PIVOT operator to the SalesData CTE to create the desired summary report.

By understanding the various components and options available with the SQL PIVOT operator, you can effectively transform and analyze your data to gain valuable insights and present your findings in a more accessible format.

SQL Unpivot: Converting Columns to Rows

The UNPIVOT operator in SQL is used to perform the opposite operation of PIVOT. It rotates columnar data into row-level data, essentially converting multiple columns into rows. This can be helpful when you need to normalize data or restructure your data for specific reporting or analysis purposes.

Syntax and key components

The syntax for using the UNPIVOT operator consists of the following key components:

Unpivoted columns

These are the columns that will be generated after the UNPIVOT operation. You need to define the column names and their data types in the SELECT statement. Assume we have the following source table called ‘ProductSales’:

YearLaptopTabletMonitor
20191200080005000
20201500090005500
202118000100006000

We want to unpivot the columns ‘Laptop’, ‘Tablet’, and ‘Monitor’ into rows. We will create two new columns: ‘Product’ and ‘Sales’. Here’s an example of how to define these columns in the SELECT statement:

SELECT
    Year,
    Product,
    Sales
FROM ...
Source query

The source query defines the initial data set on which the UNPIVOT operation will be performed. It should include the columns that you want to unpivot. 

Using the same ‘ProductSales’ table, the source query should include the columns we want to unpivot. In this case, we need the ‘Year’, ‘Laptop’, ‘Tablet’, and ‘Monitor’ columns. The source query can be written as follows:

SELECT
    Year,
    Laptop,
    Tablet,
    Monitor
FROM
    ProductSales

Combining the unpivoted columns and the source query, the complete UNPIVOT query looks like this:

SELECT
    Year,
    Product,
    Sales
FROM (
    SELECT
        Year,
        Laptop,
        Tablet,
        Monitor
    FROM
        ProductSales
) AS SourceTable
UNPIVOT (
    Sales FOR Product IN (Laptop, Tablet, Monitor)
) AS UnpivotedTable;

This query will generate the following result:

YearProductSales
2019Laptop12000
2019Tablet8000
2019Monitor5000
2020Laptop15000
2020Tablet9000
2020Monitor5500
2021Laptop18000
2021Tablet10000
2021Monitor6000

Examples

Let’s take the same table called ‘ProductSales’:

YearLaptopTabletMonitor
20191200080005000
20201500090005500
202118000100006000

To convert the ‘Laptop’, ‘Tablet’, and ‘Monitor’ columns into rows, you can use the following UNPIVOT query:

SELECT
    Year,
    Product,
    Sales
FROM (
    SELECT
        Year,
        Laptop,
        Tablet,
        Monitor
    FROM
        ProductSales
) AS SourceTable
UNPIVOT (
    Sales FOR Product IN (Laptop, Tablet, Monitor)
) AS UnpivotedTable;

The above UNPIVOT query first selects the required columns from the ‘ProductSales’ table. It then applies the UNPIVOT operator to transform the ‘Laptop’, ‘Tablet’, and ‘Monitor’ columns into rows under a new ‘Product’ column. The sales values are moved to a new ‘Sales’ column.

Additional examples and use cases

  • Unpivoting multiple columns: You can unpivot more than one set of columns by performing multiple UNPIVOT operations using subqueries or common table expressions (CTEs).
  • Filtering and sorting unpivoted data: Like with the PIVOT operator, you can apply WHERE and ORDER BY clauses to the unpivoted result set to filter and sort the data based on specific conditions.
  • Combining UNPIVOT with other SQL features: You can use UNPIVOT in conjunction with other SQL features like JOINs, subqueries, and common table expressions (CTEs) to create more complex data transformations and analysis.

Transposing Data without the PIVOT Operator

There are multiple ways to transpose data in SQL without using the PIVOT or UNPIVOT operators. Two popular alternatives are:

Transposing data using a CASE statement

Consider the following ‘ProductSales’ table:

YearProductSales
2019Laptop12000
2019Tablet8000
2019Monitor5000

To pivot this table using a CASE statement, you can write the following query:

SELECT
    Year,
    SUM(CASE WHEN Product = 'Laptop' THEN Sales END) AS Laptop,
    SUM(CASE WHEN Product = 'Tablet' THEN Sales END) AS Tablet,
    SUM(CASE WHEN Product = 'Monitor' THEN Sales END) AS Monitor
FROM
    ProductSales
GROUP BY
    Year;

This query will produce the following result:

YearLaptopTabletMonitor
20191200080005000
Transposing data using CROSS APPLY with VALUES

Let’s use the same ‘ProductSales’ table as in the previous example. To pivot the table using CROSS APPLY with VALUES, you can write the following query:

SELECT
    Year,
    Product,
    Sales
FROM
    ProductSales
CROSS APPLY (
    VALUES
        ('Laptop', Laptop),
        ('Tablet', Tablet),
        ('Monitor', Monitor)
) AS CrossApplied (Product, Sales);

This query will generate the following result:

YearProductSales
2019Laptop12000
2019Tablet8000
2019Monitor5000

Both CASE statements and CROSS APPLY with VALUES can be used to achieve the same transposing results as PIVOT and UNPIVOT operators. However, the syntax and complexity may vary depending on the database system and the specific requirements of your data analysis.

While PIVOT and UNPIVOT operators are specifically designed for transposing data and can simplify queries, they are not available in all database systems. CASE statements and CROSS APPLY with VALUES can be used as alternatives to achieve similar results in a more universally compatible way. However, these alternative methods may require more complex and lengthier queries compared to using PIVOT and UNPIVOT.

Combining Pivot and Unpivot Operations

In some scenarios, you might need to both pivot and unpivot data in a single query to perform more complex data transformations and analyses. For example, you may need to calculate summary statistics based on multiple pivoted columns, or you may need to rearrange data to make it more suitable for reporting purposes. By combining pivot and unpivot operations, you can achieve the desired data structure and format in a single query.

Example of combining Pivot and Unpivot operations in a single query

Consider a ‘Sales’ table with the following data:

ProductYearQuarterSales
Laptop2020Q15000
Laptop2020Q26000
Laptop2020Q34500
Tablet2020Q13500
Tablet2020Q24000
Tablet2020Q33000

Let’s say you want to pivot the data by quarter and then unpivot the result by product to create a table that shows the total sales for each product in 2020. You can achieve this by combining pivot and unpivot operations in a single query:

WITH PivotedData AS (
    SELECT
        Product,
        SUM(CASE WHEN Quarter = 'Q1' THEN Sales END) AS Q1,
        SUM(CASE WHEN Quarter = 'Q2' THEN Sales END) AS Q2,
        SUM(CASE WHEN Quarter = 'Q3' THEN Sales END) AS Q3
    FROM
        Sales
    WHERE
        Year = 2020
    GROUP BY
        Product
),
UnpivotedData AS (
    SELECT
        Product,
        Quarter,
        Sales
    FROM
        PivotedData
    UNPIVOT (
        Sales FOR Quarter IN (Q1, Q2, Q3)
    ) AS Unpivoted
)
SELECT
    Product,
    SUM(Sales) AS TotalSales
FROM
    UnpivotedData
GROUP BY
    Product;

This query will produce the following result:

ProductTotalSales
Laptop15500
Tablet10500

In this example, we first pivoted the data by quarter using a CASE statement and then unpivoted the result by product using the UNPIVOT operator. Finally, we aggregated the sales data to calculate the total sales for each product in 2020. By combining pivot and unpivot operations, we were able to achieve the desired data transformation and analysis in a single query.

How to Create a Pivot Table with Query Builder for SQL Server

In this section, we’ll guide you through the process of creating a pivot table using Query Builder for SQL Server. We will use the BicycleStore database as an example, demonstrating how to group, filter, and analyze data using a pivot table. By the end of this guide, you’ll be able to create your own pivot tables and gain valuable insights from your data.

Here is a short (you can read full guide here), step-by-step example of creating a pivot table and analyzing data with the help of Query Builder for SQL Server:

  1. Prerequisites: We will use the SELECT query provided above as an example. It returns total sales of bicycles grouped by categories, brands, and vendors, as well as their order status and dates.
  2. Connect a pivot table to the data source: Ensure the data source you want to display and rearrange in the pivot table is connected. The data source can be a query in the SQL or query document.
  3. Choose the right data source: Not all data source tables are suitable for conversion into a pivot table. The data source should contain a column with duplicated values that can be grouped and a column with numeric data that can be used to calculate grand totals and custom totals.
  4. Create a pivot table: Follow the steps outlined in the “To create a pivot table” section above. This includes adding fields to the pivot table, grouping data, filtering data by specific criteria, and applying conditional styles.
  5. Build a chart for the pivot table: Once the pivot table is complete, build a chart to visualize the sales data. Follow the steps in the “Build a chart for the pivot table” section above to create a pie chart based on the Grand Total column by the category Mountain Bikes.

With Query Builder for SQL Server, you can quickly and easily create pivot tables, filter data, and build charts to analyze your data. The examples provided in this article can be checked and modified using dbForge Studio’s powerful features, enabling you to write queries faster and explore a wide range of additional useful functionalities.

Conclusion

Mastering the PIVOT, UNPIVOT, and data transposing techniques is crucial for anyone working with data, as these techniques help you transform and analyze data in more meaningful ways. They enable you to convert rows to columns and vice versa, providing a better understanding of the data and allowing you to extract valuable insights. Understanding these techniques is essential for effective data analysis and reporting.

In this article, we covered various aspects of the SQL PIVOT and UNPIVOT operators, as well as alternative methods for transposing data. We discussed the syntax, key components, and use cases for both operators and provided step-by-step examples and scenarios to demonstrate their applications. Additionally, we highlighted the importance of combining PIVOT and UNPIVOT operations for more complex data transformations and analyses.

We encourage you to continue exploring various SQL features and techniques to enhance your data analysis and manipulation skills. The more you learn and practice, the better you’ll become at handling and making sense of your data.

To experiment with and validate the examples provided in this article, we recommend using dbForge Studio for SQL Server. This powerful tool helps you write queries faster and offers a multitude of additional features, making your work with SQL Server more efficient and enjoyable. With dbForge Studio for SQL Server, you can easily test, modify, and improve the examples from this article to suit your specific needs and requirements.

Happy querying!

References:

Tags: , , Last modified: April 13, 2023

About the Author /

Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training the UK and offers a full range of SQL training from introductory training to advanced administration courses.

← Previous Story
Implementing Failover in MS SQL Server 2017 Standard
→ Next Story
Identifying and Fixing Forwarded Records Performance Issue

Related Posts

SQL Percentage Calculation

How to Get SQL Percentage Calculation as Easy as Pie 

March 14, 2024• SQL Server, Work with data

Percentage calculation in SQL can be a bit like traversing an unfamiliar labyrinth. If you’re a beginner, it’s easy to get lost in a...
Read More →

Best FREE SQL Server Tools for Database Developers and Administrators!

December 07, 2023• Tools & technologies • One Comment

This article has recently been extensively revised. In particular, we reworked the article structure, and navigation, and updated...
Read More →

dbForge Edge: Your Best Universal Tool for Multidatabase Challenges

June 06, 2023• MySQL, Oracle, PostgreSQL, SQL Server, Tools & technologies

Handling several database management systems simultaneously is inevitable in working on multiple projects. At the same time, each system...
Read More →

31 Must-Know SQL Server DBA Interview Questions – Are You Prepared?

November 01, 2022• SQL Server

This article will help you to prepare for SQL Server interviews. I have explained various popular SQL Server interview questions and...
Read More →

Comments are closed.

↑
Close