PIVOT, and Where Parameters Stop Working

A pivot table turns rows into columns: one row per item, one column per month, totals in the cells. DuckDB has a PIVOT statement that does it in one query. It also has a constraint that lesson 6 introduced in a smaller form — some parts of a query must be known before values exist — and this is that rule at its most uncomfortable. Checkpoint: engine-08.

A result whose shape is data

Every query so far returned a fixed set of columns. This one cannot. Filter to January and you want one month column; filter to the full range and you want six. The shape of the result depends on the values in it.

That is why PivotResult carries its own headers:

/** Item × month pivot for the table's summary view. */
export interface PivotResult {
    columns: string[];
    rows: (string | number)[][];
}

Not an array of objects. The consumer cannot know the keys in advance, so it gets a header row and positional cells — the same reason a CSV has a header line.

The statement

PIVOT (SELECT strftime(date, '%Y-%m') AS month, item, amount::DOUBLE AS amount
       FROM sales WHERE date BETWEEN CAST(? AS DATE) AND CAST(? AS DATE))
ON month IN ('2025-01','2025-02','2025-03','2025-04','2025-05','2025-06')
USING SUM(amount) GROUP BY item ORDER BY item

Read it in three parts. The parenthesised subquery is the source, with strftime reducing each date to a YYYY-MM string. ON month IN (…) names the values that become columns. USING SUM(amount) GROUP BY item gives one row per item, with each cell the sum for that column’s month.

amount::DOUBLE is the same cast as the previous lesson, for the same reason: without it the sums arrive as decimal objects rather than numbers.

The month list cannot be bound

Now the uncomfortable part. That IN (…) list is written into the SQL text. The dates on the line above it are bound parameters. Both come from the same filter, and they are treated completely differently.

The reason is the one from lesson 6, one level up. A prepared statement is planned before its values arrive, and here the output columns depend on that list. The planner cannot describe the result of a query when it does not know how many columns it has. Bound values arrive after planning, so they are unavailable to a decision that has to happen during it.

So the list is generated:

/** Months spanned by an ISO date range, as 'YYYY-MM'. */
function monthsBetween(startISO: string, endISO: string): string[] {
    const months: string[] = [];
    const cursor = new Date(`${startISO}T00:00:00Z`);
    const end = new Date(`${endISO}T00:00:00Z`);
    cursor.setUTCDate(1);
    while (cursor <= end && months.length < 120) {
        months.push(cursor.toISOString().slice(0, 7));
        cursor.setUTCMonth(cursor.getUTCMonth() + 1);
    }
    return months;
}

And this is where the reasoning has to be explicit rather than hand-waved. The strings that reach the SQL text are not user input. They are produced by this function, from two ISO dates, in a fixed YYYY-MM format that toISOString().slice(0, 7) cannot deviate from. The dates themselves are still bound in the WHERE clause; only their derived month labels are interpolated.

That is the whole safety argument, and notice its shape: when a value must become structure, derive it from something you control rather than passing it through. The user chooses a range; you choose the labels. The 120 bound is the same instinct — a malformed range should not generate a query with ten thousand columns.

Everything is UTC on purpose. new Date('2025-03-01') parses as UTC midnight, which in a negative-offset timezone is the last day of February locally. Pin the timezone or setUTCDate(1) walks you into the wrong month for users west of Greenwich, which is a bug you will not see and they will.

Missing cells are zero, not null

An item with no sales in a month has no row to sum, and PIVOT produces NULL there. Turning that into 0 happens at the boundary:

return {
    columns: ['Item', ...months],
    rows: rows.map(r => [
        String(r.item),
        ...months.map(m => (r[m] == null ? 0 : Number(r[m]))),
    ]),
};

Reading each row by month key rather than trusting column order is deliberate — it means the returned columns array and the cells cannot drift apart. And == null catches both null and undefined, which is the one place loose equality is the right tool.

Whether missing should be zero is a judgment call. Here every cell is a sum of sales and no sales genuinely means zero, so the table stays numeric and alignable. For an average, zero would be a lie and null should survive to the renderer.

Build it

File	Action	What goes in it
`src/types.ts`	modify	`PivotResult`; add `pivotByMonth` to `DbWorkerApi`
`src/db-worker.ts`	modify	`monthsBetween` and `pivotByMonth`, with the comment explaining why the month list is interpolated; expose it
`index.html`	modify	A pivot table whose header row is filled at runtime
`src/index.ts`	modify	Build the header from `columns`, fill the body from `rows`

Done when: the pivot shows eight item rows and six month columns, and each row’s cells sum to that item’s total from the previous lesson’s totals table.

Answer key: engine-07...engine-08.

Challenge: reconcile it yourself

Add a row-total column in JavaScript and compare each against aggregateForChart’s number for that item. They must match exactly — a pivot is a rearrangement, not a recalculation, so any discrepancy means a cell was dropped or double-counted. Then narrow the date filter to a single month and confirm the table collapses to one column without any other code changing.

Next lesson: hand the whole engine to the user — free-form SQL, real error messages, and query plans.