Pivot in PostgreSQL
Introduction Pivot (or Pivot Table / Crosstab) is a technique used to transform data from rows to columns. This technique is very popular in data analysis and reporting, helping group scattered data rows into an easy-to-read summary table. Example of initial data (Long format): | Year | Quarter | Revenue | | : --- | : ------ | : ------ | | 2023 | Q1 | 100 | | 2023 | Q2 | 150 | | 2024 | Q1 | 120 | | 2024 | Q2 | 180 | Data after Pivot (Wide format): | Year | Q1 | Q2 | | : --- | : --- | : --- | | 2023 | 100 | 150 | | 2024 | 120 | 180 | How to Implement Unlike SQL Server or Oracle (which have native PIVOT syntax), PostgreSQL does not have a direct PIVOT keyword. You can accomplish this using the following common approaches: Using conditional aggregate functions ( SUM + FILTER or CASE ) is the simplest method, requiring no additional extensions and optimizing performance well. Using the tablefunc extension and the crosstab() fun...