Pivot & Unpivot

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 }
]

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 }
]

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

Unpivot Yields

Unpivot yields one binding per attribute-value pair, the value bound by AS.

SQL

create table T;

select price from unpivot {"amzn": 1900, "goog": 1120} as price;

Result

[
  1900,
  1120
]

AT Binds

AT binds the attribute name of each pair.

SQL

create table T;

select sym as sym, price as price from unpivot {"amzn": 1900, "goog": 1120} as price at sym;

Result

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

Pairs Are

Pairs are produced in object member order.

SQL

create table T;

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

Result

[
  { "sym": "c" },
  { "sym": "a" },
  { "sym": "b" }
]

AT May

AT may be omitted, binding only the value.

SQL

create table T;

select price from unpivot {"a": 10, "b": 20} as price;

Result

[
  10,
  20
]

Unpivot Dot Envelope

Select . envelopes the value and attribute bindings under their aliases.

SQL

create table T;

select . from unpivot {"a": 1, "b": 2} as v at k;

Result

[
  { "v": 1, "k": "a" },
  { "v": 2, "k": "b" }
]

Their Aliases

Select * spreads the scalar bindings under their aliases.

SQL

create table T;

select * from unpivot {"a": 1, "b": 2} as v at k;

Result

[
  { "v": 1, "k": "a" },
  { "v": 2, "k": "b" }
]

On Name

A where predicate may filter on the attribute name.

SQL

create table T;

select price from unpivot {"a": 1, "b": 2, "c": 3} as price at sym where sym != 'b';

Result

[
  1,
  3
]

Unpivot Of

Unpivot of a non-object value contributes no rows.

SQL

create table T;

select price from unpivot 5 as price at sym;

Result

[]

Unpivot Of

Unpivot of an empty object contributes no rows.

SQL

create table T;

select price from unpivot {} as price;

Result

[]

Unpivot A

Unpivot a table row's columns into (name, value) rows.

SQL

create table T;

create table closing;

insert into closing ({"date": "d1", "amzn": 1900, "goog": 1120});

select sym as sym, price as price from closing as c, unpivot c as price at sym where sym != 'date';

Result

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

Unpivot Flattens

Unpivot flattens every outer row's members in order.

SQL

create table T;

create table P;

insert into P ({"a": 1, "b": 2}, {"a": 3, "b": 4});

select sym as k, price as v from P as p, unpivot p as price at sym;

Result

[
  { "k": "a", "v": 1 },
  { "k": "b", "v": 2 },
  { "k": "a", "v": 3 },
  { "k": "b", "v": 4 }
]

Order By

Order by sorts the unpivoted pairs by the attribute name.

SQL

create table T;

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

Result

[
  1,
  2,
  3
]

An Aggregate

An aggregate folds over the unpivoted value binding.

SQL

create table T;

select sum(price) from unpivot {"a": 10, "b": 20, "c": 30} as price;

Result

[
  60
]

Count Over

Count over an unpivot source counts one row per pair.

SQL

create table T;

select count(price) from unpivot {"a": 1, "b": 2, "c": 3} as price;

Result

[
  3
]

Group By

Group by over an unpivot source groups its value binding.

SQL

create table T;

select { "v": price, "n": count(*) } from unpivot {"a": 5, "b": 5, "c": 9} as price group by price;

Result

[
  { "v": 5, "n": 2 },
  { "v": 9, "n": 1 }
]

The Unpivot

The unpivot value binding requires an alias.

SQL

create table T;

select . from unpivot {"a": 1};

Expected error: static