Unpivoting query results - Questdb

Transform wide-format data (multiple columns) into long format (rows) using UNION ALL.

Problem: Wide format to long format

You have query results with multiple columns where only one column has a value per row:

Wide format (sparse):

timestamp	symbol	buy	sell
08:10:00	ETH-USDT	NULL	3678.25
08:10:00	ETH-USDT	NULL	3678.25
08:10:00	ETH-USDT	3678.01	NULL
08:10:00	ETH-USDT	NULL	3678.00

You want to convert this to a format where side and price are explicit:

Long format (dense):

timestamp	symbol	side	price
08:10:00	ETH-USDT	sell	3678.25
08:10:00	ETH-USDT	sell	3678.25
08:10:00	ETH-USDT	buy	3678.01
08:10:00	ETH-USDT	sell	3678.00

Solution: UNION ALL with literal values

Use UNION ALL to stack columns as rows, then filter NULL values:

questdb-sql

WITH pivoted AS (
  SELECT
    timestamp,
    symbol,
    CASE WHEN side = 'buy' THEN price END as buy,
    CASE WHEN side = 'sell' THEN price END as sell
  FROM trades
  WHERE timestamp IN '$now - 5m..$now'
    AND symbol = 'ETH-USDT'
),
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;

Results:

timestamp	symbol	side	price
08:10:00	ETH-USDT	sell	3678.25
08:10:00	ETH-USDT	sell	3678.25
08:10:00	ETH-USDT	buy	3678.01
08:10:00	ETH-USDT	sell	3678.00

How it works

Step 1: Create wide format (if needed)

If your data is already in narrow format, you may need to pivot first:

sql

CASE WHEN side = 'buy' THEN price END as buy,
CASE WHEN side = 'sell' THEN price END as sell

This creates NULL values for the opposite side.

Step 2: UNION ALL

sql

SELECT timestamp, symbol, 'buy' as side, buy as price FROM pivoted
UNION ALL
SELECT timestamp, symbol, 'sell' as side, sell as price FROM pivoted

This creates two copies of every row:

First copy: Has 'buy' literal with buy column value
Second copy: Has 'sell' literal with sell column value

Step 3: Filter NULLs

sql

WHERE price IS NOT NULL

Removes rows where the price column is NULL (the opposite side).

Unpivoting multiple columns

Transform multiple numeric columns to name-value pairs:

questdb-sql

WITH sensor_data AS (
  SELECT
    timestamp,
    sensor_id,
    temperature,
    humidity,
    pressure
  FROM sensors
  WHERE timestamp IN '$now - 1h..$now'
)
SELECT timestamp, sensor_id, 'temperature' as metric, temperature as value FROM sensor_data
WHERE temperature IS NOT NULL

UNION ALL

SELECT timestamp, sensor_id, 'humidity' as metric, humidity as value FROM sensor_data
WHERE humidity IS NOT NULL

UNION ALL

SELECT timestamp, sensor_id, 'pressure' as metric, pressure as value FROM sensor_data
WHERE pressure IS NOT NULL

ORDER BY timestamp, sensor_id, metric;

Results:

timestamp	sensor_id	metric	value
10:00:00	S001	humidity	65.2
10:00:00	S001	pressure	1013.2
10:00:00	S001	temperature	22.5

Performance considerations

UNION ALL vs UNION:

sql

-- Fast: UNION ALL (no deduplication)
SELECT ... UNION ALL SELECT ...

-- Slower: UNION (deduplicates rows)
SELECT ... UNION SELECT ...

Always use UNION ALL for unpivoting unless you specifically need deduplication.

:::info Related Documentation

:::