PostgreSQL Pivot: Transform Rows into Columns
In PostgreSQL, pivoting data is not a built-in feature like in some other databases. However, you can achieve pivot functionality by combining CASE statements or using crosstab from the tablefunc module. Pivoting is essential for transforming rows into columns to make the data easier to analyze or display.
Syntax for Pivoting using crosstab
Before using crosstab, you need to enable the tablefunc extension:
-- Enable tablefunc extension CREATE EXTENSION IF NOT EXISTS tablefunc;
Syntax for crosstab:
SELECT * FROM crosstab( 'SQL query returning row identifiers and column values', 'SQL query returning a list of distinct column names' ) AS ( column1 data_type, column2 data_type, ... );
Example 1: Pivot Sales Data
Input Data
Product Quarter Sales ProductA Q1 100 ProductA Q2 200 ProductB Q1 150 Productb Q2 250Query:
Code:
-- Create pivot table using crosstab
SELECT * FROM crosstab(
$$ SELECT product, quarter, sales
FROM sales
ORDER BY product, quarter $$,
$$ SELECT DISTINCT quarter
FROM sales
ORDER BY quarter $$
) AS pivoted_table (
product TEXT,
q1_sales INT,
q2_sales INT
);
Output:
Product Q1_Sales Q2_Sales ProductA 100 200 ProductB 150 250
Example 2: Pivot Without crosstab
If tablefunc is not available, you can pivot using conditional aggregation:
Code:
-- Pivot data using CASE statements
SELECT
product,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS q1_sales,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS q2_sales
FROM sales
GROUP BY product;
Explanation:
Use Cases for Pivoting
Best Practices for Pivoting in PostgreSQL
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4