P2: SQL Server PIVOT and UNPIVOT parsing

[ajitpratap0]

Problem

PIVOT and UNPIVOT are core SQL Server features for cross-tabulation (rotating rows to columns and vice versa). Keywords are reserved in GoSQLX since v1.6.0 but parsing is not implemented. This is one of the most commonly requested SQL Server gaps.

Syntax to Support

-- PIVOT: rotate rows into columns
SELECT category, [2021], [2022], [2023]
FROM (SELECT category, year, revenue FROM sales) AS src
PIVOT (
    SUM(revenue)
    FOR year IN ([2021], [2022], [2023])
) AS pivoted;

-- UNPIVOT: rotate columns back into rows
SELECT category, year, revenue
FROM pivoted_sales
UNPIVOT (
    revenue
    FOR year IN ([2021], [2022], [2023])
) AS unpivoted;

-- Dynamic PIVOT (runtime column list)
-- Note: Dynamic PIVOT uses dynamic SQL (string execution) — not statically parseable

AST Nodes Needed

type PivotExpression struct {
    AggFunc    *FunctionCall  // SUM(revenue)
    ForColumn  *Identifier    // FOR year
    InValues   []Expression   // IN ([2021], [2022], [2023])
    Alias      string         // pivoted
}

type UnpivotExpression struct {
    ValueColumn *Identifier   // revenue
    ForColumn   *Identifier   // year
    InColumns   []*Identifier // [2021], [2022], [2023]
    Alias       string        // unpivoted
}

// Add to TableReference as a special FROM clause form
type TableReference struct {
    // ...existing fields...
    Pivot   *PivotExpression   // PIVOT(...) AS alias
    Unpivot *UnpivotExpression // UNPIVOT(...) AS alias
}

Dialect Scope

• SQL Server (T-SQL) — primary dialect
• Oracle — has similar PIVOT syntax with minor differences
• Not MySQL, PostgreSQL, SQLite (use CASE WHEN workaround)

Acceptance Criteria

• Static PIVOT syntax parsed (fixed IN column list)
• UNPIVOT syntax parsed
• AST nodes: PivotExpression, UnpivotExpression
• Formatter renders PIVOT/UNPIVOT correctly
• Active only in sqlserver and oracle dialect modes
• 20+ test cases
• docs/SQL_COMPATIBILITY.md SQL Server section updated