Snowflake: Add support for PIVOT with ANY

[StepienTomasz]

Search before asking

• I searched the issues and found no similar issues.

What Happened

SQLFluff complains about an unparsable section when pivoting with the ANY keyword 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

• I agree to follow this project's Code of Conduct