Pivot with "Others" column

QuestDB has a native PIVOT keyword for transforming rows into columns. However, when you need to pivot specific values while grouping all remaining values into an "Others" column, you need to use CASE statements instead.

Problem

You want to pivot data so that specific symbols (like EURUSD, GBPUSD, USDJPY) become columns, but also capture all other symbols in a single "Others" column:

SELECT timestamp, symbol, SUM(bid_volume) AS total_bid
FROM core_price
WHERE timestamp IN '$today'
SAMPLE BY 1m
LIMIT 20;

Results:

Solution

Use CASE statements with NOT IN for the "Others" column:

SELECT timestamp,
  SUM(CASE WHEN symbol = 'EURUSD' THEN bid_volume END) AS EURUSD,
  SUM(CASE WHEN symbol = 'GBPUSD' THEN bid_volume END) AS GBPUSD,
  SUM(CASE WHEN symbol = 'USDJPY' THEN bid_volume END) AS USDJPY,
  SUM(CASE WHEN symbol NOT IN ('EURUSD', 'GBPUSD', 'USDJPY')
    THEN bid_volume END) AS OTHERS
FROM core_price
WHERE timestamp IN '$today'
SAMPLE BY 1m
LIMIT 5;

Results:

Each timestamp now has a single row with specific symbols as columns, plus an "Others" column aggregating all remaining symbols.

When to use this pattern

Use CASE statements instead of PIVOT when you need:

• An "Others" or "Else" column to catch unspecified values
• Custom aggregation logic per column
• Different aggregation functions for different columns

For straightforward pivoting without an "Others" column, use the native PIVOT keyword.

:::info Related Documentation

• PIVOT
• CASE expressions
• SAMPLE BY aggregation :::