Pivoting Query Results

Pivoting transforms row-based data into column-based data, where values from one column become column headers. This is useful for creating wide-format reports or comparison tables.

Problem: Long-format Results​

When you aggregate data with SAMPLE BY, you get one row per time interval and grouping value:

Query returning rows per symbol and timestampDemo this query
SELECT timestamp, symbol, SUM(bid_price) AS total_bid
FROM core_price
WHERE timestamp IN today()
SAMPLE BY 1m
LIMIT 20;

Results:

timestampsymboltotal_bid
2025-12-18T00:00:00.000000ZAUDUSD1146.7547999999995
2025-12-18T00:00:00.000000ZUSDTRY77545.1637
2025-12-18T00:00:00.000000ZUSDSEK15655.122000000012
2025-12-18T00:00:00.000000ZUSDCHF1308.9189999999994
2025-12-18T00:00:00.000000ZAUDCAD1533.120900000004
2025-12-18T00:00:00.000000ZEURNZD3502.5426999999922
2025-12-18T00:00:00.000000ZAUDNZD2014.2881000000089
2025-12-18T00:00:00.000000ZUSDMXN31111.124799999983
2025-12-18T00:00:00.000000ZEURGBP1501.919500000002
2025-12-18T00:00:00.000000ZEURJPY305747.47
2025-12-18T00:00:00.000000ZUSDZAR28375.69069999998
2025-12-18T00:00:00.000000ZEURUSD2034.6741000000018
2025-12-18T00:00:00.000000ZNZDCAD1365.2795000000028
2025-12-18T00:00:00.000000ZUSDCAD2318.794500000005
2025-12-18T00:00:00.000000ZGBPNZD4033.9539000000054
2025-12-18T00:00:00.000000ZNZDUSD977.1505000000012
2025-12-18T00:00:00.000000ZUSDHKD13200.823400000027
2025-12-18T00:00:00.000000ZGBPCHF1856.3431999999962
2025-12-18T00:00:00.000000ZNZDJPY152123.41999999998
2025-12-18T00:00:00.000000ZGBPJPY348693.1200000006

This format has multiple rows per timestamp, one for each symbol.

Solution: Pivot Using CASE Statements​

To get one row per timestamp with a column for each symbol, use conditional aggregation with CASE statements:

Pivot symbols into columnsDemo this query
SELECT timestamp,
  SUM(CASE WHEN symbol='EURUSD' THEN bid_price END) AS EURUSD,
  SUM(CASE WHEN symbol='GBPUSD' THEN bid_price END) AS GBPUSD,
  SUM(CASE WHEN symbol='USDJPY' THEN bid_price END) AS USDJPY,
  SUM(CASE WHEN symbol='USDCHF' THEN bid_price END) AS USDCHF,
  SUM(CASE WHEN symbol='AUDUSD' THEN bid_price END) AS AUDUSD,
  SUM(CASE WHEN symbol='USDCAD' THEN bid_price END) AS USDCAD,
  SUM(CASE WHEN symbol='NZDUSD' THEN bid_price END) AS NZDUSD
FROM core_price
WHERE timestamp IN today()
SAMPLE BY 1m
LIMIT 5;

Now each timestamp has a single row with all symbols as columns, making cross-symbol comparison much easier.

How It Works​

The CASE statement conditionally includes values:

SUM(CASE WHEN symbol='EURUSD' THEN bid_price END) AS EURUSD

This means:

  1. For each row, if symbol='EURUSD', include the bid_price value
  2. Otherwise, include NULL (implicit)
  3. SUM() aggregates only the non-NULL values for each timestamp

The same pattern applies to each symbol, creating one column per unique value.

Use Cases​

Pivoting is useful for:

  • Comparison tables: Side-by-side comparison of metrics across categories
  • Dashboard exports: Wide-format data for spreadsheets or BI tools
  • Correlation analysis: Computing correlations between time-series in different columns
  • Report generation: Creating fixed-width reports with known categories
tip

For unknown or dynamic column lists, you'll need to generate the CASE statements programmatically in your application code. SQL doesn't support dynamic column generation.

Related Documentation