IssueTracker

What you would like to accomplish:

See docs here

Rules for pivot_column: A pivot_column must be a constant.

Restricting pivot_column to a constant severely restricts usage and flexibility of this feature.

E.g.

  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

How this might work:

(1)

  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN (
      SELECT DISTINCT quarter FROM Produce
    )
  )

(2) Alternatively (or additionally) - allow the use of the UNNEST() function in the pivot_column expression.

If applicable, reasons why alternative solutions are not sufficient:

Considered using EXECUTE IMMEDIATE and dynamic SQL but it's too unwieldy and prone to error.

Other information (workarounds you have tried, documentation consulted, etc):

Stack Overflow doesn't provide other solutions, just similar frustrations from other users with this restriction.