SQL
Simplified `PIVOT` Syntax
Multiple `GROUP BY` columns may also be provided.
Note that column names must be used rather than column positions (1, 2, etc.), and that expressions are not supported in the `GROUP BY` clause.  
```sql
PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;
```  
<div class="narrow_table"></div>  
| country |     name      | 2000 | 2010 | 2020 |
|---------|---------------|-----:|-----:|-----:|
| NL      | Amsterdam     | 1005 | 1065 | 1158 |
| US      | Seattle       | 564  | 608  | 738  |
| US      | New York City | 8015 | 8175 | 8772 |  
#### Using `PIVOT` within a `SELECT` Statement {#docs:sql:statements:pivot::using-pivot-within-a-select-statement}  
The `PIVOT` statement may be included within a `SELECT` statement as a CTE ([a Common Table Expression, or `WITH` clause](#docs:sql:query_syntax:with)), or a subquery.
This allows for a `PIVOT` to be used alongside other SQL logic, as well as for multiple `PIVOT`s to be used in one query.  
No `SELECT` is needed within the CTE, the `PIVOT` keyword can be thought of as taking its place.  
```sql
WITH pivot_alias AS (
PIVOT cities
ON year
USING sum(population)
GROUP BY country
)
SELECT * FROM pivot_alias;
```  
A `PIVOT` may be used in a subquery and must be wrapped in parentheses.
Note that this behavior is different than the SQL Standard Pivot, as illustrated in subsequent examples.  
```sql
SELECT *
FROM (
PIVOT cities
ON year
USING sum(population)
GROUP BY country
) pivot_alias;
```  
#### Multiple `PIVOT` Statements {#docs:sql:statements:pivot::multiple-pivot-statements}  
Each `PIVOT` can be treated as if it were a `SELECT` node, so they can be joined together or manipulated in other ways.  
For example, if two `PIVOT` statements share the same `GROUP BY` expression, they can be joined together using the columns in the `GROUP BY` clause into a wider pivot.  
```sql
SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);
```  
<div class="narrow_table"></div>  
| country | 2000 | 2010 | 2020 | Amsterdam | New York City | Seattle |
|---------|-----:|-----:|-----:|----------:|--------------:|--------:|
| NL      | 1005 | 1065 | 1158 | 3228      | NULL          | NULL    |
| US      | 8579 | 8783 | 9510 | NULL      | 24962         | 1910    |