Pivot

Pivot replaces the select constructor and folds the entire binding stream into a single object. Each surviving tuple contributes one member name: value. It is the dual of Unpivot.

Syntax

Railroad

pivotvalueatnamefromsource,sourcewhereexpr;

BNF

pivot-stmt ::= "pivot" expr "at" expr "from" source ( "," source )* [ where-clause ] ";"

Rules

  1. Pivot requires a from clause and yields exactly one object (one output row). (phase: evaluate last — replaces select)
  2. name must evaluate to string; tuples whose name is not a string contribute no member. (phase: evaluate last)
  3. Repeated names are last-wins across the folded stream. (phase: evaluate last)
  4. An empty input stream yields {}. (phase: evaluate last)
  5. v1 supports from and where only; order by and limit on pivot queries are deferred. (phase: evaluate last)

Examples

Minimal

Pivot Builds

Pivot builds one object with an attribute per input row.

SQL

create table T;

create table prices;

insert into prices ({"sym": "amzn", "price": 1900}, {"sym": "goog", "price": 1120});

pivot p.price at p.sym from prices as p;

Result

[
  { "amzn": 1900, "goog": 1120 }
]

Compound

Pivot Inverts

Pivot inverts unpivot over the same value.

SQL

create table T;

pivot price at sym from unpivot {"a": 1, "b": 2, "c": 3} as price at sym;

Result

[
  { "a": 1, "b": 2, "c": 3 }
]

Pivot Over

Pivot over an empty stream yields a single empty object.

SQL

create table T;

create table empty_t;

pivot p.price at p.sym from empty_t as p;

Result

[
  {}
]

Wins Duplicate

A repeated attribute name is last-wins.

SQL

create table T;

create table d;

insert into d ({"k": "x", "v": 1}, {"k": "x", "v": 2});

pivot e.v at e.k from d as e;

Result

[
  { "x": 2 }
]

String Name

A row whose AT name is not a string contributes no attribute.

SQL

create table T;

create table m;

insert into m ({"k": "ok", "v": 1}, {"k": 5, "v": 2});

pivot e.v at e.k from m as e;

Result

[
  { "ok": 1 }
]

Where Filters

Where filters which rows contribute attributes.

SQL

create table T;

create table prices;

insert into prices ({"sym": "a", "price": 1}, {"sym": "b", "price": 2}, {"sym": "c", "price": 3});

pivot p.price at p.sym from prices as p where p.price > 1;

Result

[
  { "b": 2, "c": 3 }
]

Error cases

Pivot With

Pivot with order by is not supported in v1.

SQL

create table T;

create table prices;

pivot p.price at p.sym from prices as p order by p.sym;

Expected error: static

See also