UNPIVOT table results on QuestDB

Question

I have the result of a query that looks like this. As you see I have a buy column and a sell column, but on each row only one value or the other is present, with null for the other column.

"timestamp","symbol","buy","sell"
"2024-12-19T08:10:00.700999Z","ETH-USDT",,3678.25
"2024-12-19T08:10:00.736000Z","ETH-USDT",,3678.25
"2024-12-19T08:10:00.759000Z","ETH-USDT",,3678.0
"2024-12-19T08:10:00.772999Z","ETH-USDT",,3678.0
"2024-12-19T08:10:00.887000Z","ETH-USDT",3678.01,
"2024-12-19T08:10:00.950000Z","ETH-USDT",,3678.0

I would like to convert into a format in which I have a side column and a price column, and the side is sell or buy. This would be an unpivot, but on QuestDB there is no unpivot function

"timestamp","symbol","side","price"
"2024-12-19T08:10:00.700999Z","ETH-USDT","sell",3678.25
"2024-12-19T08:10:00.736000Z","ETH-USDT","sell",3678.25
"2024-12-19T08:10:00.759000Z","ETH-USDT","sell",3678.0
"2024-12-19T08:10:00.772999Z","ETH-USDT","sell",3678.0
"2024-12-19T08:10:00.887000Z","ETH-USDT","buy",3678.01
"2024-12-19T08:10:00.950000Z","ETH-USDT","sell",3678.0

Any efficient workarounds?

Thanks!

Answer 1

Found an easy solution. I can just do a UNION ALL between both queries and then discard the null values

with pivoted AS (
 SELECT ... (my original query)
),
unpivoted AS (
  SELECT timestamp, symbol, 'buy' as side, buy as price FROM pivoted 
  UNION ALL 
  SELECT timestamp, symbol, 'sell' as side, sell as price FROM pivoted
)
SELECT * from unpivoted where price is not null order by timestamp;