SQL can't parameterize identifiers — a truly dynamic pivot needs generated SQL or the app layer.

The planner needs the output shape before it sees data — that's what makes queries checkable, composable, and cacheable — so data-driven columns are definitionally outside a static query. When column sets are closed and stable (quarters, weekdays), the pivot is honest — write the columns. When open-ended: return long form and let the chart/BI layer pivot (usually right); or two-step generation (query distinct values, build the pivot SQL — with injection/caching costs).

SELECT
  project,
  SUM(
    CASE
      WHEN color = 'red' THEN n
      ELSE 0
    END
  ) AS red,
  SUM(
    CASE
      WHEN color = 'blue' THEN n
      ELSE 0
    END
  ) AS blue
FROM
  tiles
GROUP BY
  projectORDER BY
  project;

SELECT
  project,
  color,
  SUM(n) AS n
FROM
  tiles
GROUP BY
  project,
  color
ORDER BY
  project,
  color;

The fixed pivot named red and blue, so the green tiles vanished — SQL result columns are fixed at compile time, so a column-per-value pivot can't grow with the data; long form (one row per color) handles any number.

Long form + BI-layer pivot (usual answer); two-step dynamic SQL; engine PIVOT sugar (still static columns in most engines).

A few engines allow subquery-driven IN lists in PIVOT — dialect garnish, not portable ground.