Repository: sqlfluff/sqlfluff
Issue category: Feature Request

Snowflake: Add support for PIVOT with ANY
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

SQLFluff complains about an unparsable section when pivoting with the [ANY keyword](https://docs.snowflake.com/en/sql-reference/constructs/pivot#pivot-on-all-distinct-column-values-automatically-with-dynamic-pivot) preventing automatic fixes when running `sqlfluff fix`

### Expected Behaviour

The file is parsed, automatic fixes are applied

### Observed Behaviour

When running `sqlfluff lint --dialect snowflake` the following error appears

```
sqlfluff lint pivot.sql --dialect snowflake
== [pivot.sql] FAIL                                                                                        
L:  25 | P:  20 |  PRS | Line 25, Position 20: Found unparsable section:
                       | '(sum(val) for col in (any order by col))...'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished 📜 🎉!

```

### How to reproduce

1. Create a file pivot.sql with the following content. Other rules are ignored so only the parsing error remains
```
--noqa:disable=LT09,RF04,AL05,AL01,AL03

with to_pivot as (
    select 1 as id, 'a' as col, 11 as val
    union
    select 1, 'b', 12

    union
    select 2, 'a', 11
    union
    select 2, 'b', 12

    union
    select 3, 'a', 11
    union
    select 3, 'b', 12

    union
    select 4, 'a', 11
    union
    select 4, 'b', 12
)

select *
from to_pivot pivot(sum(val) for col in (any order by col))
order by id;

```
2. Run `sqlfluff lint pivot.sql --dialect snowflake`

### Dialect

Snowflake

### Version

sqlfluff, version 3.0.5
Python 3.11.7

### Configuration

default

### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)



Functions to edit:
src/sqlfluff/dialects/dialect_snowflake.py:FromPivotExpressionSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_snowflake.py b/src/sqlfluff/dialects/dialect_snowflake.py
index 525fe5f613b..1d18b7855bc 100644
--- a/src/sqlfluff/dialects/dialect_snowflake.py
+++ b/src/sqlfluff/dialects/dialect_snowflake.py
@@ -1718,7 +1718,10 @@ class FromBeforeExpressionSegment(BaseSegment):
 
 
 class FromPivotExpressionSegment(BaseSegment):
-    """A PIVOT expression."""
+    """A PIVOT expression.
+
+    https://docs.snowflake.com/en/sql-reference/constructs/pivot.html
+    """
 
     type = "from_pivot_expression"
     match_grammar = Sequence(
@@ -1728,7 +1731,16 @@ class FromPivotExpressionSegment(BaseSegment):
             "FOR",
             Ref("SingleIdentifierGrammar"),
             "IN",
-            Bracketed(Delimited(Ref("LiteralGrammar"))),
+            Bracketed(
+                OneOf(
+                    Delimited(Ref("LiteralGrammar")),
+                    Sequence("ANY", Ref("OrderByClauseSegment", optional=True)),
+                    Ref("SelectStatementSegment"),
+                )
+            ),
+            Sequence(
+                "DEFAULT", "ON", "NULL", Bracketed(Ref("LiteralGrammar")), optional=True
+            ),
         ),
     )
 

Test patch: diff --git a/test/fixtures/dialects/snowflake/pivot.sql b/test/fixtures/dialects/snowflake/pivot.sql
index 4292ad63fd2..af410c23be5 100644
--- a/test/fixtures/dialects/snowflake/pivot.sql
+++ b/test/fixtures/dialects/snowflake/pivot.sql
@@ -17,3 +17,52 @@ from table_a
 unpivot (a for b in (col_1, col_2, col_3))
 unpivot (c for d in (col_a, col_b, col_c))
 ;
+
+-- from Snowflake's PIVOT docs
+SELECT *
+FROM quarterly_sales
+  PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
+ORDER BY empid;
+
+
+-- from Snowflake's PIVOT docs
+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;
+
+-- from Snowflake's PIVOT docs
+SELECT *
+FROM quarterly_sales
+  PIVOT(SUM(amount) FOR quarter IN (
+    '2023_Q1',
+    '2023_Q2',
+    '2023_Q3',
+    '2023_Q4')
+  ) AS p (empid_renamed, Q1, Q2, Q3, Q4)
+ORDER BY empid_renamed;
+
+-- from Snowflake's PIVOT docs
+SELECT *
+FROM quarterly_sales
+  PIVOT(SUM(amount)
+    FOR quarter IN (
+      '2023_Q1',
+      '2023_Q2',
+      '2023_Q3',
+      '2023_Q4',
+      '2024_Q1')
+    DEFAULT ON NULL (0)
+  )
+ORDER BY empid;
+
+
+-- https://github.com/sqlfluff/sqlfluff/issues/5876
+select *
+from to_pivot pivot(sum(val) for col in (any order by col))
+order by id;
diff --git a/test/fixtures/dialects/snowflake/pivot.yml b/test/fixtures/dialects/snowflake/pivot.yml
index a23080af6ed..ed2c8f15d50 100644
--- a/test/fixtures/dialects/snowflake/pivot.yml
+++ b/test/fixtures/dialects/snowflake/pivot.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 08999c2dfbc20dda5139fbc1f40ef4518a16170975f0691dca45771711c548c3
+_hash: 56bfa91af39ff5f68aa1628e31776d244512b7fc07ca4bda598bbcb6c6abf43f
 file:
 - statement:
     select_statement:
@@ -202,3 +202,292 @@ file:
               - end_bracket: )
             - end_bracket: )
 - statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: quarterly_sales
+          from_pivot_expression:
+            keyword: PIVOT
+            bracketed:
+            - start_bracket: (
+            - function:
+                function_name:
+                  function_name_identifier: SUM
+                function_contents:
+                  bracketed:
+                    start_bracket: (
+                    expression:
+                      column_reference:
+                        naked_identifier: amount
+                    end_bracket: )
+            - keyword: FOR
+            - naked_identifier: quarter
+            - keyword: IN
+            - bracketed:
+                start_bracket: (
+                keyword: ANY
+                orderby_clause:
+                - keyword: ORDER
+                - keyword: BY
+                - column_reference:
+                    naked_identifier: quarter
+                end_bracket: )
+            - end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          naked_identifier: empid
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: quarterly_sales
+          from_pivot_expression:
+            keyword: PIVOT
+            bracketed:
+            - start_bracket: (
+            - function:
+                function_name:
+                  function_name_identifier: SUM
+                function_contents:
+                  bracketed:
+                    start_bracket: (
+                    expression:
+                      column_reference:
+                        naked_identifier: amount
+                    end_bracket: )
+            - keyword: FOR
+            - naked_identifier: quarter
+            - keyword: IN
+            - bracketed:
+                start_bracket: (
+                select_statement:
+                  select_clause:
+                    keyword: SELECT
+                    select_clause_modifier:
+                      keyword: DISTINCT
+                    select_clause_element:
+                      column_reference:
+                        naked_identifier: quarter
+                  from_clause:
+                    keyword: FROM
+                    from_expression:
+                      from_expression_element:
+                        table_expression:
+                          table_reference:
+                            naked_identifier: ad_campaign_types_by_quarter
+                  where_clause:
+                    keyword: WHERE
+                    expression:
+                      column_reference:
+                        naked_identifier: television
+                      comparison_operator:
+                        raw_comparison_operator: '='
+                      boolean_literal: 'TRUE'
+                  orderby_clause:
+                  - keyword: ORDER
+                  - keyword: BY
+                  - column_reference:
+                      naked_identifier: quarter
+                end_bracket: )
+            - end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          naked_identifier: empid
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: quarterly_sales
+          from_pivot_expression:
+            keyword: PIVOT
+            bracketed:
+            - start_bracket: (
+            - function:
+                function_name:
+                  function_name_identifier: SUM
+                function_contents:
+                  bracketed:
+                    start_bracket: (
+                    expression:
+                      column_reference:
+                        naked_identifier: amount
+                    end_bracket: )
+            - keyword: FOR
+            - naked_identifier: quarter
+            - keyword: IN
+            - bracketed:
+              - start_bracket: (
+              - quoted_literal: "'2023_Q1'"
+              - comma: ','
+              - quoted_literal: "'2023_Q2'"
+              - comma: ','
+              - quoted_literal: "'2023_Q3'"
+              - comma: ','
+              - quoted_literal: "'2023_Q4'"
+              - end_bracket: )
+            - end_bracket: )
+          alias_expression:
+            keyword: AS
+            naked_identifier: p
+            bracketed:
+              start_bracket: (
+              identifier_list:
+              - naked_identifier: empid_renamed
+              - comma: ','
+              - naked_identifier: Q1
+              - comma: ','
+              - naked_identifier: Q2
+              - comma: ','
+              - naked_identifier: Q3
+              - comma: ','
+              - naked_identifier: Q4
+              end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          naked_identifier: empid_renamed
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: quarterly_sales
+          from_pivot_expression:
+            keyword: PIVOT
+            bracketed:
+            - start_bracket: (
+            - function:
+                function_name:
+                  function_name_identifier: SUM
+                function_contents:
+                  bracketed:
+                    start_bracket: (
+                    expression:
+                      column_reference:
+                        naked_identifier: amount
+                    end_bracket: )
+            - keyword: FOR
+            - naked_identifier: quarter
+            - keyword: IN
+            - bracketed:
+              - start_bracket: (
+              - quoted_literal: "'2023_Q1'"
+              - comma: ','
+              - quoted_literal: "'2023_Q2'"
+              - comma: ','
+              - quoted_literal: "'2023_Q3'"
+              - comma: ','
+              - quoted_literal: "'2023_Q4'"
+              - comma: ','
+              - quoted_literal: "'2024_Q1'"
+              - end_bracket: )
+            - keyword: DEFAULT
+            - keyword: 'ON'
+            - keyword: 'NULL'
+            - bracketed:
+                start_bracket: (
+                numeric_literal: '0'
+                end_bracket: )
+            - end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          naked_identifier: empid
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: from
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: to_pivot
+          from_pivot_expression:
+            keyword: pivot
+            bracketed:
+            - start_bracket: (
+            - function:
+                function_name:
+                  function_name_identifier: sum
+                function_contents:
+                  bracketed:
+                    start_bracket: (
+                    expression:
+                      column_reference:
+                        naked_identifier: val
+                    end_bracket: )
+            - keyword: for
+            - naked_identifier: col
+            - keyword: in
+            - bracketed:
+                start_bracket: (
+                keyword: any
+                orderby_clause:
+                - keyword: order
+                - keyword: by
+                - column_reference:
+                    naked_identifier: col
+                end_bracket: )
+            - end_bracket: )
+      orderby_clause:
+      - keyword: order
+      - keyword: by
+      - column_reference:
+          naked_identifier: id
+- statement_terminator: ;