1

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

Returns:

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
2

You can use a self-join for that:

select ni.date,
       ni.stock_id,
       ni.report_value as net_income, 
       nr.report_value as total_revenue
from report_items ni
  join report_items nr 
    on ni.stock_id = nr.stock_id 
   and ni."date" = nr."date" 
   and nr.report_item = 'totalRevenue'
where ni.report_item = 'netIncome'
order by date desc limit 100

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.