Unpivoting Query Results

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):

timestampsymbolbuysell
08:10:00ETH-USDTNULL3678.25
08:10:00ETH-USDTNULL3678.25
08:10:00ETH-USDT3678.01NULL
08:10:00ETH-USDTNULL3678.00

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

Long format (dense):

timestampsymbolsideprice
08:10:00ETH-USDTsell3678.25
08:10:00ETH-USDTsell3678.25
08:10:00ETH-USDTbuy3678.01
08:10:00ETH-USDTsell3678.00

Solution: UNION ALL with Literal Values​

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

UNPIVOT buy/sell columns to side/price rowsDemo this query
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 >= dateadd('m', -5, 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:

timestampsymbolsideprice
08:10:00ETH-USDTsell3678.25
08:10:00ETH-USDTsell3678.25
08:10:00ETH-USDTbuy3678.01
08:10:00ETH-USDTsell3678.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:

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​

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​

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:

UNPIVOT sensor readingsDemo this query
WITH sensor_data AS (
  SELECT
    timestamp,
    sensor_id,
    temperature,
    humidity,
    pressure
  FROM sensors
  WHERE timestamp >= dateadd('h', -1, 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:

timestampsensor_idmetricvalue
10:00:00S001humidity65.2
10:00:00S001pressure1013.2
10:00:00S001temperature22.5

Performance Considerations​

UNION ALL vs UNION:

-- 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.

Reverse: Pivot (Long to Wide)​

To go back from long to wide format, use aggregation with CASE:

SELECT
  timestamp,
  sensor_id,
  MAX(CASE WHEN metric = 'temperature' THEN value END) as temperature,
  MAX(CASE WHEN metric = 'humidity' THEN value END) as humidity,
  MAX(CASE WHEN metric = 'pressure' THEN value END) as pressure
FROM sensor_readings_long
GROUP BY timestamp, sensor_id;

See the Pivoting guide for more details.

Related Documentation