So I have my data organized as:
id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date 1 | netIncome | 100 | stockid1 | 2019-05-01 2 | totalRevenue |1000 | stockid1 | 2019-05-01 3 | salesExpense |20 | stockid1 | 2019-05-01 4 | totalAssets | 10000 | stockid1 | 2019-05-01 5 | netIncome | 30 | stockid2 | 2019-04-23 6 | totalRevenue |600 | stockid2 | 2019-04-23 7 | salesExpense |15 | stockid2 | 2019-04-23 8 | totalAssets | 900 | stockid3 | 2019-04-23
So for each stockid, date pair, there is only one of each different report_item (there's not 2 different totalAssets for X date, Y stock id). There is a totalAssets for each stockID for every quarter (which aren't always on the same date).
What I want to do is return stock_id, date, totalAssets, totalRevenue as columns, effectively merging the 2 rows (one for totalAssets, totalRevenue) for each date/stock_id pair into 1 while discarding any rows where report_item is neither totalAssets or totalRevenue.
So this is what I did to turn turn the totalAssets and totalRevenue rows into columns, which gets rid of rows with other report_items, although for each stock_id, date pair there are still 2 rows. What I want is to have them in one row.
select date, stock_id, (case when report_item = 'netIncome' then id end) as netIncome, (case when report_item = 'totalRevenue' then id end) as totalRevenue from report_items where report_item IN('netIncome', 'totalRevenue') order by date desc limit 100
date | stock_id | netIncome | totalRevenue 2019-05-01 | stockid1 | 100 | null 2019-05-01 | stockid1 | null | 1000 2019-04-23 | stockid2 | 30 | null 2019-04-23 | stockid2 | null | 600