PIVOT TABLES IN SQL

If you are quite familiar with Power Query in Microsoft PowerBI or Microsoft Excel, then you probably know what it means to pivot a table. Pivoting allows you to turn rows into columns in order to analyze and summarize the data. It basically aggregates the data into the specified aggregate function.

Below is also a pictorial representation of a pivot table.

As you delve deeper into other tools like SQL, you realize that there are several ways to achieve pivot tables for analysis. This article is to show how pivoting is done in SQL while exploring two major methods.

PIVOT TABLE METHODS

To explore different method of extracting pivot tables for different type of SQL, we need to setup our table in our database.

DROP DATABASE IF EXISTS pivot_article;
CREATE DATABASE pivot_article;
USE pivot_article;

CREATE TABLE internet_sales(
   ProductName     VARCHAR(32) NOT NULL,
   Amount          DECIMAL(10,2) NOT NULL,
   ProductCategory  VARCHAR(32) NOT NULL
);
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product A',200,'Category1');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product B',100,'Category2');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product B',150,'Category2');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product A',120,'Category1');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product C',135,'Category3');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product D',200,'Category4');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product D',150,'Category2');
INSERT INTO internet_sales(ProductName,Amount,ProductCategory) VALUES ('Product E',125,'Category3');

The Corresponding table return the tables as thus;

Internet Sales table created from MySQL

Extraction of pivot tables is quite different for different SQL types, we are going to consider two major SQL types

1. For MSSQL, you can write a pivot table query to pivot the values of a column into separate columns, using the internet_sales table as a case study. For example, your manager asked you to get the total sales amount for each category. You can pivot and and can done using this query;

 SELECT
    ProductName,
    [Category1],
    [Category2],
    [Category3],
    [Category4]
FROM
    (SELECT
        ProductName,
        Amount,
        ProductCategory
    FROM
       internet_sales) AS sales
PIVOT
    (SUM(Amount)
    FOR ProductCategory IN ([Category1], [Category2], 
                           [Category3],[Category4])) 
                         AS PivotTable;

The result thus gives the table below

2. For MySQL Instead of Pivot clause, CASE WHEN is use to generate a pivot table for the Product Category and the code is expressed as thus;

SELECT
  ProductName,
  SUM(CASE WHEN ProductCategory = 'Category1' 
            THEN Amount ELSE 0 END) AS Category1,
  SUM(CASE WHEN ProductCategory = 'Category2' 
            THEN Amount ELSE 0 END) AS Category2,
  SUM(CASE WHEN ProductCategory = 'Category3' 
            THEN Amount ELSE 0 END) AS Category3,
  SUM(CASE WHEN ProductCategory = 'Category4' 
            THEN Amount ELSE 0 END) AS Category4
FROM
  internet_sales
GROUP BY
  ProductName;

The result which is expected to be as same as the case of MSSQL is returned as thus;

CONCLUSION

In conclusion, a pivot table in SQL can be used to summarize and present data in a more organized and structured manner, making it easier to derive insights and perform analysis based on specific categories or dimensions.