A PIVOTin SQL is used to transform data from rows into columns, allowing you to reorganize and summarize information in a more readable format. It is particularly useful for creating cross-tab reports and is supported by SQL Server and Oracle databases, among others. Here, we'll discuss how to use the PIVOT operator and achieve similar results in other databases.
Example Table: Sales Data
Let's consider a table sales with the following structure:
sales
sale_id sale_date product quantity
1 2023-01-01 Product A 10
2 2023-01-01 Product B 20
3 2023-01-02 Product A 15
4 2023-01-02 Product C 25
Goal
We want to transform this table to show total quantities sold per product for each sale date.
Using PIVOTin SQL Server
In SQL Server, the PIVOToperator can be used as follows:
SELECT sale_date, [Product A], [Product B], [Product C]
FROM
(
SELECT sale_date, product, quantity
FROM sales
) AS SourceTable
PIVOT
(
SUM(quantity)
FOR product IN ([Product A], [Product B], [Product C])
) AS PivotTable;
Result
sale_date Product A Product B Product C
2023-01-01 10 20 NULL
2023-01-02 15 NULL 25
Using Conditional Aggregation in MySQL
MySQL does not have a PIVOToperator, but you can achieve similar results using conditional aggregation:
SELECT
sale_date,
SUM(CASE WHEN product = 'Product A' THEN quantity ELSE 0 END) AS `Product A`,
SUM(CASE WHEN product = 'Product B' THEN quantity ELSE 0 END) AS `Product B`,
SUM(CASE WHEN product = 'Product C' THEN quantity ELSE 0 END) AS `Product C`
FROM sales
GROUP BY sale_date;
Result
sale_date Product A Product B Product C
2023-01-01 10 20 0
2023-01-02 15 0 25
Using PIVOTin Oracle
In Oracle, the PIVOT clause works similarly to SQL Server:
SELECT sale_date, "Product A", "Product B", "Product C"
FROM
(
SELECT sale_date, product, quantity
FROM sales
)
PIVOT
(
SUM(quantity)
FOR product IN ('Product A' AS "Product A", 'Product B' AS "Product B", 'Product C' AS "Product C")
);
Result
sale_date Product A Product B Product C
2023-01-01 10 20 NULL
2023-01-02 15 NULL 25
Using Crosstab in PostgreSQL
PostgreSQL can achieve similar results using the crosstab function provided by the tablefuncextension:
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM crosstab(
'SELECT sale_date, product, quantity FROM sales ORDER BY 1,2'
) AS ct(sale_date date, "Product A" int, "Product B" int, "Product C" int);
Result
sale_date Product A Product B Product C
2023-01-01 10 20 NULL
2023-01-02 15 NULL 25
Using the PIVOTfunctionality in SQL can greatly simplify the process of reorganizing data from a long format (rows) to a wide format (columns). While the PIVOToperator is directly available in SQL Server and Oracle, similar results can be achieved in MySQL and PostgreSQL using conditional aggregation and the crosstab function, respectively.
