What is PIVOT in Databases?
PIVOT is a technique used in SQL to transform row data into columns, making it useful for reporting and data visualization. Different databases implement pivoting differently.
1. Example Table (Sales Data)
Before explaining the implementation, consider the following Sales table:
| Product | Year | Sales |
|---|---|---|
| A | 2023 | 100 |
| B | 2023 | 200 |
| A | 2024 | 150 |
| B | 2024 | 250 |
Expected Output (Pivoted Table):
| Product | 2023 | 2024 |
|---|---|---|
| A | 100 | 150 |
| B | 200 | 250 |
2. PIVOT in SQL Server
Example:
SELECT Product, [2023], [2024]
FROM (
SELECT Product, Year, Sales FROM Sales
) source_table
PIVOT (
SUM(Sales) FOR Year IN ([2023], [2024])
) pivot_table;
3. PIVOT in Oracle
Example:
SELECT * FROM (
SELECT Product, Year, Sales FROM Sales
) PIVOT (
SUM(Sales) FOR Year IN (2023, 2024)
);
4. PIVOT in MySQL & PostgreSQL
MySQL and PostgreSQL do not have a built-in PIVOT function. Instead, you can use CASE with GROUP BY to achieve similar results.
Example:
SELECT Product,
SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS "2023",
SUM(CASE WHEN Year = 2024 THEN Sales ELSE 0 END) AS "2024"
FROM Sales
GROUP BY Product;
5. Dynamic Pivot Implementation
SQL Server Dynamic PIVOT:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Year), ',') FROM (SELECT DISTINCT Year FROM Sales) AS Years;
SET @query = '
SELECT Product, ' + @cols + '
FROM (
SELECT Product, Year, Sales FROM Sales
) source_table
PIVOT (
SUM(Sales) FOR Year IN (' + @cols + ')
) pivot_table;';
EXEC sp_executesql @query;
MySQL Dynamic Pivot Using Prepared Statements:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(
'SUM(CASE WHEN Year = ', Year, ' THEN Sales ELSE 0 END) AS "', Year, '"')
) INTO @sql FROM Sales;
SET @sql = CONCAT('SELECT Product, ', @sql, ' FROM Sales GROUP BY Product;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
PostgreSQL Pivot Using crosstab() (Requires tablefunc Extension):
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
'SELECT Product, Year, SUM(Sales) FROM Sales GROUP BY Product, Year'
) AS ct(Product TEXT, "2023" INT, "2024" INT);
6. Summary of Database Pivot Implementation
| Database | Pivot Support | Alternative |
|---|---|---|
| SQL Server | PIVOT operator | Dynamic SQL with STRING_AGG |
| Oracle | PIVOT operator | CASE with GROUP BY |
| MySQL | No built-in PIVOT | CASE with GROUP BY or Dynamic SQL |
| PostgreSQL | No built-in PIVOT | CASE with GROUP BY or crosstab() (if tablefunc extension is enabled) |
This document provides an overview of how to implement pivot tables in different databases, including static and dynamic approaches, along with examples of a Sales table transformation.