Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres Transpose Rows to Columns Based on Column Value

I have the following statement table in Postgres:

stock |  year | statement    | amount           

ACME  | 2003  | Q1 Earnings  | 100
ACME  | 2003  | Q2 Earnings  | 200
ACME  | 2004  | Q2 Earnings  | 300 

How do I make a new table with all of a year's 4 quarters on one row? And null values for missing statements.

stock | year | Q1 Earnings | Q2 Earnings | Q3 Earnings | Q4 Earnings 

ACME  | 2003 | 100         | 200         | Null        | Null
ACME  | 2004 | NULL        | 300         | Null        | Null

Found this answer: Postgres - Transpose Rows to Columns but it doesn't show how to create and populate the new columns based on another value or deal with null values.

like image 988
user2415706 Avatar asked Sep 05 '25 00:09

user2415706


1 Answers

Assuming you have a fixed 4 quarters per year which you want to display, use pivoting logic:

SELECT
    stock,
    year,
    MAX(amount) FILTER (WHERE statement = 'Q1 Earnings') AS "Q1 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q2 Earnings') AS "Q2 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q3 Earnings') AS "Q3 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q4 Earnings') AS "Q4 Earnings"
FROM statement
GROUP BY
    stock,
    year;
like image 169
Tim Biegeleisen Avatar answered Sep 07 '25 20:09

Tim Biegeleisen