To enroll a project in the pipe syntax preview, fill out the pipe syntax enrollment form.
GoogleSQL supports pipe query syntax, which is a simpler and more concise alternative to standard query syntax. Pipe syntax supports many of the same operators as standard syntax, and improves some areas of SQL query functionality.
For more background and details on the language design, see the research paper SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL.
Pipe syntax
Pipe syntax has the following key characteristics:
- Each pipe operator in pipe syntax consists of the pipe symbol,
|>
, an operator name, and any arguments:
|> operator_name argument_list
- Pipe operators can be added to the end of any valid query.
- Pipe operators can be applied in any order, any number of times.
- Pipe syntax works anywhere standard syntax is supported: in queries, views, table-valued functions (TVFs), and other contexts.
- Pipe syntax can be mixed with standard syntax in the same query. For example, subqueries can use different syntax from the parent query.
- A query can start with a
FROM
clause, and pipe operators can optionally be added after theFROM
clause.
Query comparison
Consider the following table called Produce
:
CREATE OR REPLACE TABLE Produce AS (
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
UNION ALL
SELECT 'carrots' AS item, 8 AS sales, 'vegetable' AS category
UNION ALL
SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
UNION ALL
SELECT 'bananas' AS item, 5 AS sales, 'fruit' AS category
);
SELECT * FROM Produce;
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 2 | fruit |
| carrots | 8 | vegetable |
| apples | 7 | fruit |
| bananas | 5 | fruit |
+---------+-------+-----------*/
Compare the following equivalent queries that compute the number and total
amount of sales for each item in the Produce
table:
Standard syntax
SELECT item, COUNT(*) AS num_items, SUM(sales) AS total_sales
FROM Produce
WHERE
item != 'bananas'
AND category IN ('fruit', 'nut')
GROUP BY item
ORDER BY item DESC;
/*--------+-----------+-------------+
| item | num_items | total_sales |
+--------+-----------+-------------+
| apples | 2 | 9 |
+--------+-----------+-------------*/
Pipe syntax
FROM Produce
|> WHERE
item != 'bananas'
AND category IN ('fruit', 'nut')
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales
GROUP BY item
|> ORDER BY item DESC;
/*--------+-----------+-------------+
| item | num_items | total_sales |
+--------+-----------+-------------+
| apples | 2 | 9 |
+--------+-----------+-------------*/
Pipe operator semantics
Pipe operators have the following semantic behavior:
- Each pipe operator performs a self-contained operation.
- A pipe operator consumes the input table passed to it through the pipe
symbol,
|>
, and produces a new table as output. - A pipe operator can reference only columns from its immediate input table. Columns from earlier in the same query aren't visible. Inside subqueries, correlated references to outer columns are still allowed.
FROM
queries
In pipe syntax, a query can start with a standard FROM
clause
and use any standard FROM
syntax, including tables, joins, subqueries,
and
table-valued functions (TVFs). Table aliases can be
assigned to each input item using the AS alias
clause.
A query with only a FROM
clause, like FROM table_name
, is allowed in pipe
syntax and returns all rows from the table. For tables with columns,
FROM table_name
in pipe syntax is similar to
SELECT * FROM table_name
in standard syntax.
Examples
The following queries use the Produce
table:
FROM Produce;
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 2 | fruit |
| carrots | 8 | vegetable |
| apples | 7 | fruit |
| bananas | 5 | fruit |
+---------+-------+-----------*/
-- Join tables in the FROM clause and then apply pipe operators.
FROM
Produce AS p1
JOIN Produce AS p2
USING (item)
|> WHERE item = "bananas"
|> SELECT p1.item, p2.sales;
/*---------+-------+
| item | sales |
+---------+-------+
| bananas | 5 |
+---------+-------*/
Pipe operators
GoogleSQL supports the following pipe operators. For operators that correspond or relate to similar operations in standard syntax, the operator descriptions highlight similarities and differences and link to more detailed documentation on the corresponding syntax.
Pipe operator list
Name | Summary |
---|---|
SELECT
|
Produces a new table with the listed columns. |
EXTEND
|
Propagates the existing table and adds computed columns. |
SET
|
Replaces the values of columns in the current table. |
DROP
|
Removes listed columns from the current table. |
RENAME
|
Renames specified columns. |
AS
|
Introduces a table alias for the input table. |
WHERE
|
Filters the results of the input table. |
LIMIT
|
Limits the number of rows to return in a query, with an optional
OFFSET clause to skip over rows.
|
AGGREGATE
|
Performs aggregation on data across groups of rows or the full input table. |
ORDER BY
|
Sorts results by a list of expressions. |
JOIN
|
Joins rows from the input table with rows from a second table provided as an argument. |
CALL
|
Calls a table-valued function (TVF), passing the pipe input table as a table argument. |
WINDOW
|
Adds columns with the result of computing the function over some window of existing rows |
TABLESAMPLE
|
Selects a random sample of rows from the input table. |
PIVOT
|
Rotates rows into columns. |
UNPIVOT
|
Rotates columns into rows. |
SELECT
pipe operator
|> SELECT expression [[AS] alias] [, ...]
Description
Produces a new table with the listed columns, similar to the outermost
SELECT
clause in a table subquery in standard syntax.
Supports standard output modifiers like SELECT AS STRUCT
, and supports
window functions. Doesn't support aggregations or
anonymization.
In pipe syntax, the SELECT
operator in a query is optional. The SELECT
operator can be used near the end of a query to specify the list of output
columns. The final query result contains the columns returned from the last pipe
operator. If the SELECT
operator isn't used to select specific columns, the
output includes the full row, similar to what the
SELECT *
statement in standard syntax produces.
In pipe syntax, the SELECT
clause doesn't perform aggregation. Use the
AGGREGATE
operator instead.
For cases where SELECT
would be used in standard syntax to rearrange columns,
pipe syntax supports other operators:
- The
EXTEND
operator adds columns. - The
SET
operator updates the value of an existing column. - The
DROP
operator removes columns. - The
RENAME
operator renames columns.
Example
FROM (SELECT 'apples' AS item, 2 AS sales)
|> SELECT item AS fruit_name;
/*------------+
| fruit_name |
+------------+
| apples |
+------------*/
EXTEND
pipe operator
|> EXTEND expression [[AS] alias] [, ...]
Description
Propagates the existing table and adds a computed column, similar to
SELECT *, new_column
in standard syntax. Supports
window functions.
Examples
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> EXTEND item IN ('carrots', 'oranges') AS is_orange;
/*---------+-------+------------+
| item | sales | is_orange |
+---------+-------+------------+
| apples | 2 | FALSE |
| carrots | 8 | TRUE |
+---------+-------+------------*/
-- Window function, with `OVER`
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> EXTEND SUM(sales) OVER() AS total_sales;
/*---------+-------+-------------+
| item | sales | total_sales |
+---------+-------+-------------+
| apples | 2 | 15 |
| bananas | 5 | 15 |
| carrots | 8 | 15 |
+---------+-------+-------------*/
SET
pipe operator
|> SET column_name = expression [, ...]
Description
Replaces the value of a column in the current table, similar to
SELECT * REPLACE (expression AS column)
in standard syntax.
Each referenced column must exist exactly once in the input table.
After a SET
operation, the referenced top-level columns (like x
) are
updated, but table aliases (like t
) still refer to the original row values.
Therefore, t.x
will still refer to the original value.
Example
(
SELECT 1 AS x, 11 AS y
UNION ALL
SELECT 2 AS x, 22 AS y
)
|> SET x = x * x, y = 3;
/*---+---+
| x | y |
+---+---+
| 1 | 3 |
| 4 | 3 |
+---+---*/
FROM (SELECT 2 AS x, 3 AS y) AS t
|> SET x = x * x, y = 8
|> SELECT t.x AS original_x, x, y;
/*------------+---+---+
| original_x | x | y |
+------------+---+---+
| 2 | 4 | 8 |
+------------+---+---*/
DROP
pipe operator
|> DROP column_name [, ...]
Description
Removes listed columns from the current table, similar to
SELECT * EXCEPT (column)
in standard syntax. Each
referenced column must exist at least once in the input table.
After a DROP
operation, the referenced top-level columns (like x
) are
removed, but table aliases (like t
) still refer to the original row values.
Therefore, t.x
will still refer to the original value.
Example
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
|> DROP sales, category;
/*--------+
| item |
+--------+
| apples |
+--------*/
FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x
|> SELECT t.x AS original_x, y;
/*------------+---+
| original_x | y |
+------------+---+
| 1 | 2 |
+------------+---*/
RENAME
pipe operator
|> RENAME old_column_name [AS] new_column_name [, ...]
Description
Renames specified columns. Each column to be renamed must exist exactly once in
the input table. The RENAME
operator can't rename value table fields,
pseudo-columns, range variables, or objects that aren't columns in the input
table.
After a RENAME
operation, the referenced top-level columns (like x
) are
renamed, but table aliases (like t
) still refer to the original row
values. Therefore, t.x
will still refer to the original value.
Example
SELECT 1 AS x, 2 AS y, 3 AS z
|> AS t
|> RENAME y AS renamed_y
|> SELECT *, t.y AS t_y;
/*---+-----------+---+-----+
| x | renamed_y | z | t_y |
+---+-----------+---+-----+
| 1 | 2 | 3 | 2 |
+---+-----------+---+-----*/
AS
pipe operator
|> AS alias
Description
Introduces a table alias for the input table, similar to applying the
AS alias
clause on a table subquery in standard syntax. Any
existing table aliases are removed and the new alias becomes the table alias for
all columns in the row.
The AS
operator can be useful after operators like
SELECT
, EXTEND
, or
AGGREGATE
that add columns but can't give table
aliases to them. You can use the table alias to disambiguate columns after the
JOIN
operator.
Example
(
SELECT "000123" AS id, "apples" AS item, 2 AS sales
UNION ALL
SELECT "000456" AS id, "bananas" AS item, 5 AS sales
) AS sales_table
|> AGGREGATE SUM(sales) AS total_sales GROUP BY id, item
-- The sales_table alias is now out of scope. We must introduce a new one.
|> AS t1
|> JOIN (SELECT 456 AS id, "yellow" AS color) AS t2
ON CAST(t1.id AS INT64) = t2.id
|> SELECT t2.id, total_sales, color;
/*-----+-------------+--------+
| id | total_sales | color |
+-----+-------------+--------+
| 456 | 5 | yellow |
+-----+-------------+--------*/
WHERE
pipe operator
|> WHERE boolean_expression
Description
Filters the results of the input table. The WHERE
operator behaves the same
as the WHERE
clause in standard syntax.
In pipe syntax, the WHERE
operator also replaces the
HAVING
clause and QUALIFY
clause in
standard syntax. For example, after performing aggregation with the
AGGREGATE
operator, use the WHERE
operator
instead of the HAVING
clause. For window functions inside
a QUALIFY
clause, use window functions inside a WHERE
clause instead.
Example
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> WHERE sales >= 3;
/*---------+-------+
| item | sales |
+---------+-------+
| bananas | 5 |
| carrots | 8 |
+---------+-------*/
LIMIT
pipe operator
|> LIMIT count [OFFSET skip_rows]
Description
Limits the number of rows to return in a query, with an optional OFFSET
clause
to skip over rows. The LIMIT
operator behaves the same as the
LIMIT
and OFFSET
clause in standard syntax.
Examples
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> ORDER BY item
|> LIMIT 1;
/*---------+-------+
| item | sales |
+---------+-------+
| apples | 2 |
+---------+-------*/
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> ORDER BY item
|> LIMIT 1 OFFSET 2;
/*---------+-------+
| item | sales |
+---------+-------+
| carrots | 8 |
+---------+-------*/
AGGREGATE
pipe operator
-- Full-table aggregation
|> AGGREGATE aggregate_expression [[AS] alias] [, ...]
-- Aggregation with grouping
|> AGGREGATE [aggregate_expression [[AS] alias] [, ...]]
GROUP BY groupable_items [[AS] alias] [, ...]
-- Aggregation with grouping and shorthand ordering syntax |> AGGREGATE [aggregate_expression [[AS] alias] [order_suffix] [, ...]] GROUP [AND ORDER] BY groupable_item [[AS] alias] [order_suffix] [, ...] order_suffix: {ASC | DESC} [{NULLS FIRST | NULLS LAST}]
Description
Performs aggregation on data across grouped rows or an entire table. The
AGGREGATE
operator is similar to a query in standard syntax that contains a
GROUP BY
clause or a SELECT
list with
aggregate functions or both. In pipe syntax, the
GROUP BY
clause is part of the AGGREGATE
operator. Pipe syntax
doesn't support a standalone GROUP BY
operator.
Without the GROUP BY
clause, the AGGREGATE
operator performs full-table
aggregation and produces one output row.
With the GROUP BY
clause, the AGGREGATE
operator performs aggregation with
grouping, producing one row for each set of distinct values for the grouping
expressions.
The AGGREGATE
expression list corresponds to the aggregated expressions in a
SELECT
list in standard syntax. Each expression in the AGGREGATE
list must
include an aggregate function. Aggregate expressions can also include scalar
expressions (for example, sqrt(SUM(x*x))
). Column aliases can be assigned
using the AS
operator. Window functions aren't allowed,
but the EXTEND
operator can be used before the
AGGREGATE
operator to compute window functions.
The GROUP BY
clause in the AGGREGATE
operator corresponds to the GROUP BY
clause in standard syntax. Unlike in standard syntax, aliases can be assigned to
GROUP BY
items. Standard grouping operators like GROUPING SETS
, ROLLUP
,
and CUBE
are supported.
The output columns from the AGGREGATE
operator include all grouping columns
first, followed by all aggregate columns, using their assigned aliases as the
column names.
Unlike in standard syntax, grouping expressions aren't repeated across SELECT
and GROUP BY
clauses. In pipe syntax, the grouping expressions are listed
once, in the GROUP BY
clause, and are automatically included as output columns
for the AGGREGATE
operator.
Because output columns are fully specified by the AGGREGATE
operator, the
SELECT
operator isn't needed after the AGGREGATE
operator unless
you want to produce a list of columns different from the default.
Standard syntax
-- Aggregation in standard syntax SELECT SUM(col1) AS total, col2, col3, col4... FROM table1 GROUP BY col2, col3, col4...
Pipe syntax
-- The same aggregation in pipe syntax
FROM table1
|> AGGREGATE SUM(col1) AS total
GROUP BY col2, col3, col4...
Examples
-- Full-table aggregation
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'apples' AS item, 7 AS sales
)
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales;
/*-----------+-------------+
| num_items | total_sales |
+-----------+-------------+
| 3 | 14 |
+-----------+-------------*/
-- Aggregation with grouping
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'apples' AS item, 7 AS sales
)
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales
GROUP BY item;
/*---------+-----------+-------------+
| item | num_items | total_sales |
+---------+-----------+-------------+
| apples | 2 | 9 |
| bananas | 1 | 5 |
+---------+-----------+-------------*/
Shorthand ordering syntax with AGGREGATE
The AGGREGATE
operator supports a shorthand ordering syntax, which is
equivalent to applying the ORDER BY
operator as part
of the AGGREGATE
operator without repeating the column list:
-- Aggregation with grouping and shorthand ordering syntax |> AGGREGATE [aggregate_expression [[AS] alias] [order_suffix] [, ...]] GROUP [AND ORDER] BY groupable_item [[AS] alias] [order_suffix] [, ...] order_suffix: {ASC | DESC} [{NULLS FIRST | NULLS LAST}]
The GROUP AND ORDER BY
clause is equivalent to an ORDER BY
clause on all
groupable_items
. By default, each groupable_item
is sorted in ascending
order with NULL
values first. Other ordering suffixes like DESC
or NULLS
LAST
can be used for other orders.
Without the GROUP AND ORDER BY
clause, the ASC
or DESC
suffixes can be
added on individual columns in the GROUP BY
list or AGGREGATE
list or both.
The NULLS FIRST
and NULLS LAST
suffixes can be used to further modify NULL
sorting.
Adding these suffixes is equivalent to adding an ORDER BY
clause that includes
all of the suffixed columns with the suffixed grouping columns first, matching
the left-to-right output column order.
Examples
Consider the following table called Produce
:
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 2 | fruit |
| carrots | 8 | vegetable |
| apples | 7 | fruit |
| bananas | 5 | fruit |
+---------+-------+-----------*/
The following two equivalent examples show you how to order by all grouping
columns using the GROUP AND ORDER BY
clause or a separate ORDER BY
clause:
-- Order by all grouping columns using GROUP AND ORDER BY.
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP AND ORDER BY category, item DESC;
/*-----------+---------+-------------+
| category | item | total_sales |
+-----------+---------+-------------+
| fruit | bananas | 5 |
| fruit | apples | 9 |
| vegetable | carrots | 8 |
+-----------+---------+-------------*/
--Order by columns using ORDER BY after performing aggregation.
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP BY category, item
|> ORDER BY category, item DESC;
You can add an ordering suffix to a column in the AGGREGATE
list. Although the
AGGREGATE
list appears before the GROUP BY
list in the query, ordering
suffixes on columns in the GROUP BY
list are applied first.
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales ASC
GROUP BY item, category DESC;
/*---------+-----------+-------------+
| item | category | total_sales |
+---------+-----------+-------------+
| carrots | vegetable | 8 |
| bananas | fruit | 5 |
| apples | fruit | 9 |
+---------+-----------+-------------*/
The previous query is equivalent to the following:
-- Order by specified grouping and aggregate columns.
FROM Produce
|> AGGREGATE SUM(sales) AS total_sales
GROUP BY item, category
|> ORDER BY category DESC, total_sales;
ORDER BY
pipe operator
|> ORDER BY expression [sort_options] [, ...]
Description
Sorts results by a list of expressions. The ORDER BY
operator behaves the same
as the ORDER BY
clause in standard syntax. Suffixes like
ASC
, DESC
, and NULLS LAST
are supported for customizing the ordering for
each expression.
In pipe syntax, the AGGREGATE
operator also
supports shorthand ordering suffixes to
apply ORDER BY
behavior more concisely as part of aggregation.
Example
(
SELECT 1 AS x
UNION ALL
SELECT 3 AS x
UNION ALL
SELECT 2 AS x
)
|> ORDER BY x DESC;
/*---+
| x |
+---+
| 3 |
| 2 |
| 1 |
+---*/
JOIN
pipe operator
|> [join_type] JOIN from_item [[AS] alias] [{on_clause | using_clause}]
Description
Joins rows from the input table with rows from a second table provided as an
argument. The JOIN
operator behaves the same as the
JOIN
operation in standard syntax. The input table is the
left side of the join and the JOIN
argument is the right side of the join.
Standard join inputs are supported, including tables, subqueries, UNNEST
operations, and table-valued function (TVF) calls. Standard join modifiers like
LEFT
, INNER
, and CROSS
are allowed before the JOIN
keyword.
An alias can be assigned to the input table on the right side of the join, but
not to the input table on the left side of the join. If an alias on the
input table is needed, perhaps to disambiguate columns in an
ON
expression, then an alias can be added using the
AS
operator before the JOIN
arguments.
Example
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
)
|> AS produce_sales
|> LEFT JOIN
(
SELECT "apples" AS item, 123 AS id
) AS produce_data
ON produce_sales.item = produce_data.item
|> SELECT produce_sales.item, sales, id;
/*---------+-------+------+
| item | sales | id |
+---------+-------+------+
| apples | 2 | 123 |
| bananas | 5 | NULL |
+---------+-------+------*/
CALL
pipe operator
|> CALL table_function (argument [, ...]) [[AS] alias]
Description
Calls a table-valued function (TVF), similar to table function calls in standard syntax.
TVFs in standard syntax can be called in the FROM
clause or in a JOIN
operation. These are both allowed in pipe syntax as well.
In pipe syntax, TVFs that take a table argument can also be called with the
CALL
operator. The first table argument comes from the input table and
must be omitted in the arguments. An optional table alias can be added for the
output table.
Multiple TVFs can be called sequentially without using nested subqueries.
Examples
Suppose you have TVFs with the following parameters:
tvf1(inputTable1 ANY TABLE, arg1 ANY TYPE)
andtvf2(arg2 ANY TYPE, arg3 ANY TYPE, inputTable2 ANY TABLE)
.
The following examples compare calling both TVFs on an input table
by using standard syntax and by using the CALL
pipe operator:
-- Call the TVFs without using the CALL operator.
SELECT *
FROM
tvf2(arg2, arg3, TABLE tvf1(TABLE input_table, arg1));
-- Call the same TVFs with the CALL operator.
FROM input_table
|> CALL tvf1(arg1)
|> CALL tvf2(arg2, arg3);
WINDOW
pipe operator
|> WINDOW window_expression [[AS] alias] [, ...]
Description
Adds a column with the result of computing the function over some window of
existing rows, similar to calling window functions in a
SELECT
list in standard syntax. Existing rows and columns are unchanged. The
window expression must include a window function with an
OVER
clause.
Alternatively, you can use the EXTEND
operator for
window functions.
Example
(
SELECT 'apples' AS item, 2 AS sales
UNION ALL
SELECT 'bananas' AS item, 5 AS sales
UNION ALL
SELECT 'carrots' AS item, 8 AS sales
)
|> WINDOW SUM(sales) OVER() AS total_sales;
/*---------+-------+-------------+
| item | sales | total_sales |
+---------+-------+-------------+
| apples | 2 | 15 |
| bananas | 5 | 15 |
| carrots | 8 | 15 |
+---------+-------+-------------*/
TABLESAMPLE
pipe operator
|> TABLESAMPLE SYSTEM (percent PERCENT)
Description
Selects a random sample of rows from the input table. The TABLESAMPLE
pipe
operator behaves the same as TABLESAMPLE
operator in
standard syntax.
Example
The following example samples approximately 1% of data from a table called
LargeTable
:
FROM LargeTable
|> TABLESAMPLE SYSTEM (1 PERCENT);
PIVOT
pipe operator
|> PIVOT (aggregate_expression FOR input_column IN (pivot_column [, ...])) [[AS] alias]
Description
Rotates rows into columns. The PIVOT
pipe operator behaves the same as the
PIVOT
operator in standard syntax.
Example
(
SELECT "kale" AS product, 51 AS sales, "Q1" AS quarter
UNION ALL
SELECT "kale" AS product, 4 AS sales, "Q1" AS quarter
UNION ALL
SELECT "kale" AS product, 45 AS sales, "Q2" AS quarter
UNION ALL
SELECT "apple" AS product, 8 AS sales, "Q1" AS quarter
UNION ALL
SELECT "apple" AS product, 10 AS sales, "Q2" AS quarter
)
|> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2'));
/*---------+----+------+
| product | Q1 | Q2 |
+---------+-----------+
| kale | 55 | 45 |
| apple | 8 | 10 |
+---------+----+------*/
UNPIVOT
pipe operator
|> UNPIVOT (values_column FOR name_column IN (column_to_unpivot [, ...])) [[AS] alias]
Description
Rotates columns into rows. The UNPIVOT
pipe operator behaves the same as the
UNPIVOT
operator in standard syntax.
Example
(
SELECT 'kale' as product, 55 AS Q1, 45 AS Q2
UNION ALL
SELECT 'apple', 8, 10
)
|> UNPIVOT(sales FOR quarter IN (Q1, Q2));
/*---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| kale | 55 | Q1 |
| kale | 45 | Q2 |
| apple | 8 | Q1 |
| apple | 10 | Q2 |
+---------+-------+---------*/