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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With