How to Use SQL PIVOT

The Quick Answer: How to Pivot in SQL

The SQL Server PIVOT operator is useful when summarizing data since it allows for the transformation of rows into columns. Consider the city_sales table below, which shows the general sales of a product across five major US cities.

Example of table to transform using SQL PIVOT. Image by Author.

We will use the following query, which uses the PIVOT operator, to pivot multiple columns in the above table.

-- Select the columns for the output: city and sales data for 2019, 2020, and 2021
SELECT 
    city,
    [2019] AS Sales_2019,
    [2020] AS Sales_2020,
    [2021] AS Sales_2021
FROM
    (
        -- Subquery to select city, year, and sales from city_sales table
        SELECT city, year, sales 
        FROM city_sales
    ) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns and sum the sales for each year
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt;

Example output transformation using SQL PIVOT. Image by Author.

What is PIVOT in SQL

Pivoting is a technique in SQL that is used to transform rows into columns in tabular data. In SQL Server and Oracle, pivoting is done with the PIVOT operator. The syntax for the SQL PIVOT operator, which is shown below, has three main parts:

• SELECT: The SELECT statement references the columns to return in the SQL pivot table.

• Subquery: The subquery contains the data source or table to be included in the SQL pivot table.

• PIVOT: The PIVOT operator contains the aggregations and filter to be applied in the pivot table.

-- Select the non-pivoted column and the pivoted columns with aliases
SELECT 
    [non-pivoted column],               
    [first pivoted column] AS [column name],   
    [second pivoted column] AS [column name],  
    ...
FROM
    (
        -- Subquery to select the necessary columns from the source table
        SELECT [columns] 
        FROM [source_table]
    ) AS source_table
PIVOT
(
    -- Pivot operation to aggregate data and transform rows into columns
    [aggregate_function]([pivot_column])        
    FOR [pivot_column] IN ([first pivoted column], [second pivoted column], ...) 
) AS pivot_table; -- Alias for the result of the pivot operation

Database-Specific Implementations of SQL PIVOT

SQL Server and Oracle databases directly support the PIVOT operator. However, MySQL and PostgreSQL have alternative methods for creating pivot tables in SQL.

PIVOT in SQL Server

SQL Server provides native support for the PIVOT operator. Here, we will use the PIVOT operator to transform rows into columns and summarize data using aggregate functions like SUM(). We will also use SQL clauses, such as WHERE, GROUP BY, and ORDER BY for more refined data manipulation.

The example below demonstrates how to use the PIVOT operator to filter data for the year 2020 or later (WHERE), group data by city and year (GROUP BY), and sort the data by city (ORDER BY):

-- Select the city and sales data for the years 2019, 2020, and 2021
SELECT 
    city,                         
    [2019] AS Sales_2019,             
    [2020] AS Sales_2020,           
    [2021] AS Sales_2021              
FROM
    (
        -- Subquery to select city, year, and sales from the city_sales table
        SELECT city, year, sales 
        FROM city_sales
        WHERE year >= 2020 -- filtering
        GROUP BY city, year, sales -- grouping
    ) AS src                   
PIVOT
(
    -- Pivot the sales data to have years as columns, averaging the sales over each year
    SUM(sales) -- aggregating
    FOR year IN ([2019], [2020], [2021]) 
) AS pvt;         

Example output of transformed table using SQL PIVOT with common clauses. Image by Author.

PIVOT in Oracle

Similar to SQL Server, Oracle also uses the PIVOT operator to transform rows into columns. However, the syntax of the PIVOT operator in the Oracle database differs slightly from that in SQL Server. The query below shows how the PIVOT operator appears in Oracle. Note that the columns are aliased within the PIVOT operator, unlike the outer SELECT statement in SQL Server.

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

Pivoting in MySQL

The MySQL database does not support the SQL PIVOT operator. To create SQL pivot tables in MySQL, you must use the CASE statement with conditional aggregation. For example, the query below will create a pivot table to aggregate the data by the sum of sales for different years, grouped and ordered by city.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019,
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales 
GROUP BY 
    city
ORDER BY 
    city; 

Pivoting in PostgreSQL

The PostgreSQL database also does not support the SQL PIVOT operator. Therefore, when creating pivot tables, it is important to use the CASE statement with conditional aggregation. The query below is an example of the conditional CASE statements used to create pivot tables in PostgreSQL.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019, 
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales
GROUP BY 
    city
ORDER BY 
    city; 

Advanced Techniques with SQL PIVOT

There are some advanced SQL pivoting techniques for writing complex queries. In this section, we will look at dynamic pivoting, which we use to create queries for pivot tables where the columns to pivot are unknown. This method uses SQL to generate the pivot table at runtime.

Dynamic PIVOT in SQL Server

The query below uses PIVOT to dynamically pivot the year column in SQL Server. The query will retrieve distinct years from the city_sales table. It will then construct and execute a dynamic PIVOT query using the retrieved years.

-- Declare variables to hold the column names and the dynamic query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);
-- Get distinct values of the year column and concatenate them into a string
SELECT @cols = STRING_AGG(QUOTENAME(year), ',') 
               FROM (SELECT DISTINCT year FROM city_sales) AS years;
-- Construct the dynamic PIVOT query
SET @query = '
SELECT city, ' + @cols + '
FROM
(
    -- Subquery to select city, year, and sales from the city_sales table
    SELECT city, year, sales
    FROM city_sales
) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns, summing the sales for each year
    SUM(sales)
    FOR year IN (' + @cols + ')
) AS pvt
ORDER BY city';  -- Order the results by city
-- Execute the dynamic PIVOT query
EXEC sp_executesql @query;

Example output of table using SQL dynamic PIVOT. Image by Author.

Dynamic PIVOT in Oracle

In the Oracle database, dynamic pivoting is supported by executing the dynamic query using the EXECUTE IMMEDIATE statement. The LISTAGG function is also used to dynamically aggregate the column names and single quotes ' ' used in aliases within the pivot.

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Get the list of years dynamically
    SELECT LISTAGG('''' || year || ''' AS ' || 'sales_' || year, ',') 
    INTO cols
    FROM (SELECT DISTINCT year FROM city_sales);
    -- Construct the dynamic SQL query
    sql_query := 'SELECT * FROM (
                      SELECT city, year, sales
                      FROM city_sales
                  )
                  PIVOT (
                      SUM(sales)
                      FOR year IN (' || cols || ')
                  )
                  ORDER BY city';
    -- Execute the dynamic SQL query
    EXECUTE IMMEDIATE sql_query;
END;

Dynamic Pivoting in MySQL

MySQL does not support direct dynamic SQL. Thus, you must create a stored procedure for dynamic PIVOT in MySQL. The query below shows how to use the stored procedure to create a dynamic PIVOT query.

-- Declare variables to hold the dynamic columns (cols) and the final SQL query
DELIMITER $
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE cols VARCHAR(1000);
    DECLARE sql_query VARCHAR(2000);
    -- Get the list of distinct years
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS ', year, '')
    ) INTO cols
    FROM city_sales;
    -- Construct the dynamic SQL query
    SET sql_query = CONCAT('SELECT city, ', cols, ' FROM city_sales GROUP BY city ORDER BY city');
    -- Prepare and execute the SQL query
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

After creating the stored procedure, you must call the stored procedure to execute the dynamic PIVOT query:

CALL dynamic_pivot();

Dynamic Pivoting in PostgreSQL

Similarly, you can create a dynamic PIVOT in PostgreSQL using the following query:

-- Block declaration to execute PL/pgSQL code in an anonymous code block
DO
$
DECLARE
    cols text;  -- Variable to store the list of columns for the dynamic query
    query text; -- Variable to store the dynamic SQL query
BEGIN
    -- Get distinct years and construct the list of SUM(CASE...) statements
    SELECT STRING_AGG(DISTINCT 'SUM(CASE WHEN year = ' || year || ' THEN sales ELSE 0 END) AS "Sales_' || year || '"', ', ')
    INTO cols
    FROM city_sales;
    -- Construct the dynamic PIVOT query
    query := 'SELECT city, ' || cols || ' FROM city_sales GROUP BY city ORDER BY city';
    -- Execute the dynamic PIVOT query
    EXECUTE query;
END
$;

Conclusion and Further Learning

Understanding how to use PIVOT in SQL is important if you want to transform and analyze data efficiently. When creating pivot tables in SQL, learning the different implementations of the PIVOT operator in the different databases is crucial. As a data analyst, I encourage you to continue practicing your SQL skills to learn how and when to apply PIVOT to analyze different datasets.

If you are either an aspiring data analyst looking to get a foothold in the industry or a more seasoned analyst, I recommend taking DataCamp’s Introduction to SQL and Intermediate SQL courses to improve your data analysis skills. I also recommend taking our Data Manipulation in SQL course, which teaches subqueries and other concepts covered in this tutorial, along with our Introduction to SQL Server course, which covers SQL Server specifically.