SQL
Simplified `PIVOT` Syntax
The full syntax diagram is below, but the simplified `PIVOT` syntax can be summarized using spreadsheet pivot table naming conventions as:  
```sql
PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩
ORDER BY ⟨columns_with_order_directions⟩
LIMIT ⟨number_of_rows⟩;
```  
The `ON`, `USING`, and `GROUP BY` clauses are each optional, but they may not all be omitted.  
#### Example Data {#docs:sql:statements:pivot::example-data}  
All examples use the dataset produced by the queries below:  
```sql
CREATE TABLE cities (
country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
('NL', 'Amsterdam', 2000, 1005),
('NL', 'Amsterdam', 2010, 1065),
('NL', 'Amsterdam', 2020, 1158),
('US', 'Seattle', 2000, 564),
('US', 'Seattle', 2010, 608),
('US', 'Seattle', 2020, 738),
('US', 'New York City', 2000, 8015),
('US', 'New York City', 2010, 8175),
('US', 'New York City', 2020, 8772);
```  
```sql
SELECT *
FROM cities;
```  
<div class="narrow_table"></div>  
| country |     name      | year | population |
|---------|---------------|-----:|-----------:|
| NL      | Amsterdam     | 2000 | 1005       |
| NL      | Amsterdam     | 2010 | 1065       |
| NL      | Amsterdam     | 2020 | 1158       |
| US      | Seattle       | 2000 | 564        |
| US      | Seattle       | 2010 | 608        |
| US      | Seattle       | 2020 | 738        |
| US      | New York City | 2000 | 8015       |
| US      | New York City | 2010 | 8175       |
| US      | New York City | 2020 | 8772       |  
#### `PIVOT ON` and `USING` {#docs:sql:statements:pivot::pivot-on-and-using}  
Use the `PIVOT` statement below to create a separate column for each year and calculate the total population in each.
The `ON` clause specifies which column(s) to split into separate columns.
It is equivalent to the columns parameter in a spreadsheet pivot table.  
The `USING` clause determines how to aggregate the values that are split into separate columns.
This is equivalent to the values parameter in a spreadsheet pivot table.
If the `USING` clause is not included, it defaults to `count(*)`.  
```sql
PIVOT cities
ON year
USING sum(population);
```  
<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 |  
In the above example, the `sum` aggregate is always operating on a single value.
If we only want to change the orientation of how the data is displayed without aggregating, use the `first` aggregate function.
In this example, we are pivoting numeric values, but the `first` function works very well for pivoting out a text column.
(This is something that is difficult to do in a spreadsheet pivot table, but easy in DuckDB!)  
This query produces a result that is identical to the one above:  
```sql
PIVOT cities
ON year
USING first(population);
```  
> **Note.  ** The SQL syntax permits [`FILTER` clauses](#docs:sql:query_syntax:filter) with aggregate functions in the `USING` clause.
> In DuckDB, the `PIVOT` statement currently does not support these and they are silently ignored.  
#### `PIVOT ON`, `USING`, and `GROUP BY` {#docs:sql:statements:pivot::pivot-on-using-and-group-by}  
By default, the `PIVOT` statement retains all columns not specified in the `ON` or `USING` clauses.
To include only certain columns and further aggregate, specify columns in the `GROUP BY` clause.
This is equivalent to the rows parameter of a spreadsheet pivot table.  
In the below example, the `name` column is no longer included in the output, and the data is aggregated up to the `country` level.  
```sql
PIVOT cities
ON year
USING sum(population)
GROUP BY country;
```  
<div class="narrow_table"></div>  
| country | 2000 | 2010 | 2020 |
|---------|-----:|-----:|-----:|
| NL      | 1005 | 1065 | 1158 |
| US      | 8579 | 8783 | 9510 |  
#### `IN` Filter for `ON` Clause {#docs:sql:statements:pivot::in-filter-for-on-clause}  
To only create a separate column for specific values within a column in the `ON` clause, use an optional `IN` expression.
Let's say for example that we wanted to forget about the year 2020 for no particular reason...  
```sql
PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;
```  
<div class="narrow_table"></div>  
| country | 2000 | 2010 |
|---------|-----:|-----:|
| NL      | 1005 | 1065 |
| US      | 8579 | 8783 |  
#### Multiple Expressions per Clause {#docs:sql:statements:pivot::multiple-expressions-per-clause}  
Multiple columns can be specified in the `ON` and `GROUP BY` clauses, and multiple aggregate expressions can be included in the `USING` clause.