Pivot: Add limitations section

duckdb · Nov 13, 2024 · 219c1f7 · 219c1f7
1 parent 98e4a53
commit 219c1f7
Showing 1 changed file with 28 additions and 0 deletions.
diff --git a/docs/sql/statements/pivot.md b/docs/sql/statements/pivot.md
@@ -364,3 +364,31 @@ PIVOT (
 Below is the full syntax diagram of the SQL Standard version of the `PIVOT` statement.
 
 <div id="rrdiagram2"></div>
+
+## Limitations
+
+`PIVOT` currently only accepts an aggregate function, expressions are not allowed.
+For example, the following query attempts to get the population as the number of people instead of thousands of people (i.e., instead of 564, get 564000):
+
+```sql
+PIVOT cities
+ON year
+USING sum(population) * 1000;
+```
+
+However, it fails with the following error:
+
+```console
+Catalog Error: * is not an aggregate function
+```
+
+To work around this limitation, perform the `PIVOT` with the aggregation only, then use the [`COLUMNS` expression]({% link docs/sql/expressions/star.md %}#columns-expression):
+
+```sql
+SELECT country, name, 1000 * COLUMNS(* EXCLUDE (country, name))
+FROM (
+    PIVOT cities
+    ON year
+    USING sum(population)
+);
+```