How to Perform Pivot Operations in PostgreSQL?

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

Query:

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:

• SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) sums sales for Q1.
• This approach works without enabling any extensions.

Use Cases for Pivoting

• Summarizing Data: Transform transactional data into reports.
• Data Visualization: Easier to visualize in tools like Tableau or Excel.
• Comparative Analysis: Compare metrics across categories.

Best Practices for Pivoting in PostgreSQL

• Use crosstab for Efficiency: Handles large datasets better than CASE.
• Order Data Correctly: Ensure queries return data in the expected order for crosstab.
• Dynamic Columns: For dynamic pivots, consider using procedural languages like PL/pgSQL.

All PostgreSQL Questions, Answers, and Code Snippets Collection.