Repository: tobymao/sqlglot

Issue:
[Snowflake][Parse] Dynamic `PIVOT` query parsed/optimized incorrectly
**Fully reproducible code snippet**

```
example_pivot_q = """
SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;
"""
dialect = Snowflake()
pivot_exp = parse_one(example_pivot_q, dialect=dialect)
optimized_exp = optimize(pivot_exp, dialect=dialect)

print(pivot_exp.sql(dialect=dialect))
# returns
# 'SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY ORDER BY quarter)) ORDER BY empid'
print(optimized_exp.sql(dialect=dialect))
# 'SELECT * FROM "QUARTERLY_SALES" AS "QUARTERLY_SALES" PIVOT(SUM("QUARTERLY_SALES"."AMOUNT") FOR IN (ANY ORDER BY "QUARTERLY_SALES"."QUARTER")) AS "_q_0" ORDER BY "_q_0"."EMPID"'
```

**Official Documentation**
https://docs.snowflake.com/en/sql-reference/constructs/pivot#pivot-on-all-distinct-column-values-automatically-with-dynamic-pivot



Discussion:

Patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 746712b6..7ab3416a 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1917,8 +1917,6 @@ class Generator(metaclass=_Generator):
         direction = self.seg("UNPIVOT" if expression.unpivot else "PIVOT")
 
         field = self.sql(expression, "field")
-        if field and isinstance(expression.args.get("field"), exp.PivotAny):
-            field = f"IN ({field})"
 
         include_nulls = expression.args.get("include_nulls")
         if include_nulls is not None:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 9ea81837..f22de203 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3907,13 +3907,12 @@ class Parser(metaclass=_Parser):
             self.raise_error("Expecting IN (")
 
         if self._match(TokenType.ANY):
-            expr: exp.PivotAny | exp.In = self.expression(exp.PivotAny, this=self._parse_order())
+            exprs: t.List[exp.Expression] = ensure_list(exp.PivotAny(this=self._parse_order()))
         else:
-            aliased_expressions = self._parse_csv(_parse_aliased_expression)
-            expr = self.expression(exp.In, this=value, expressions=aliased_expressions)
+            exprs = self._parse_csv(_parse_aliased_expression)
 
         self._match_r_paren()
-        return expr
+        return self.expression(exp.In, this=value, expressions=exprs)
 
     def _parse_pivot(self) -> t.Optional[exp.Pivot]:
         index = self._index


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index b82f9928..9c036c76 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -107,10 +107,10 @@ WHERE
             "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (SELECT DISTINCT quarter FROM ad_campaign_types_by_quarter WHERE television = TRUE ORDER BY quarter)) ORDER BY empid"
         )
         self.validate_identity(
-            "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY ORDER BY quarter)) ORDER BY empid"
+            "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)) ORDER BY empid"
         )
         self.validate_identity(
-            "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY)) ORDER BY empid"
+            "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY)) ORDER BY empid"
         )
         self.validate_identity(
             "MERGE INTO my_db AS ids USING (SELECT new_id FROM my_model WHERE NOT col IS NULL) AS new_ids ON ids.type = new_ids.type AND ids.source = new_ids.source WHEN NOT MATCHED THEN INSERT VALUES (new_ids.new_id)"