Support DuckDB's "simplified" UNPIVOT syntax

[Bilbottom]

Summary

DuckDB has implemented a "simplified" syntax for PIVOT and UNPIVOT statements (in addition to the standard SQL syntax).

The PIVOT statement can be parsed by SQLGlot, but not the UNPIVOT statements.

Corresponding DuckDB documentation for UNPIVOT:

• https://duckdb.org/docs/sql/statements/unpivot

MWE

I tried parsing each example on the docs linked above using the latest version of SQLGlot (main), and all six of them threw an error for different reasons -- but running them directly in DuckDB works fine, so the queries are valid

# sqlglot @ git+https://github.com/tobymao/sqlglot@07d05da95c7d3882a7032dade3cbeefbd96628b7
import sqlglot

statements = [
    # https://duckdb.org/docs/sql/statements/unpivot#unpivot-manually
    """
    UNPIVOT monthly_sales
    ON jan, feb, mar, apr, may, jun
    INTO
        NAME month
        VALUE sales;
    """,
    # https://duckdb.org/docs/sql/statements/unpivot#unpivot-dynamically-using-columns-expression
    """
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales;
    """,
    # https://duckdb.org/docs/sql/statements/unpivot#unpivot-into-multiple-value-columns
    """
    UNPIVOT monthly_sales
    ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
    INTO
        NAME quarter
        VALUE month_1_sales, month_2_sales, month_3_sales;
    """,
    # https://duckdb.org/docs/sql/statements/unpivot#using-unpivot-within-a-select-statement (1)
    """
    WITH unpivot_alias AS (
        UNPIVOT monthly_sales
        ON COLUMNS(* EXCLUDE (empid, dept))
        INTO
            NAME month
            VALUE sales
    )
    SELECT * FROM unpivot_alias;
    """,
    # https://duckdb.org/docs/sql/statements/unpivot#using-unpivot-within-a-select-statement (2)
    """
    SELECT *
    FROM (
        UNPIVOT monthly_sales
        ON COLUMNS(* EXCLUDE (empid, dept))
        INTO
            NAME month
            VALUE sales
    ) unpivot_alias;
    """,
    # https://duckdb.org/docs/sql/statements/unpivot#expressions-within-unpivot-statements
    """
    UNPIVOT (SELECT 42 AS col1, 'woot' AS col2)
    ON
        (col1 * 2)::VARCHAR,
        col2;
    """,
]

for i, sql in enumerate(statements):
    try:
        sqlglot.parse(sql, read="duckdb", dialect="duckdb")
        print(f"statement {i} parse succeeded")
    except Exception as err:
        print(f"statement {i} parse failed: {err}")