Unpivot
Unpivot is a from-source that ranges over the attribute–value pairs of an object. Each pair binds its value under the required as alias and its attribute name under the optional at alias. It is the dual of Pivot.
Syntax
Railroad
BNF
unpivot-source ::= "unpivot" expr "as" identifier [ "at" identifier ]
Rules
- The value alias (
as) is required; the attribute-name alias (at) is optional. (phase: evaluate as part of from) - Each object member produces one output row with the value and name bindings. (phase: evaluate as part of from)
- When
expris not an object, unpivot yields no rows (inner-join semantics). (phase: evaluate as part of from) - Unpivot may appear inline in a comma-separated from list and may reference lateral bindings from preceding sources. (phase: evaluate as part of from)
Examples
Minimal
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
]
Compound
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 }
]
Error cases
The Unpivot
The unpivot value binding requires an alias.
SQL
create table T;
select . from unpivot {"a": 1};
Expected error: static