PIVOT Proposal

[Puchaczov]

Overview

SQL PIVOT operations transform data from rows to columns, making it easier to generate cross-tabulated results. Currently, Musoq lacks native pivoting capabilities.

The PIVOT operation would enable users to:

• Rotate row data into columns
• Create dynamic cross-tabulations
• Simplify complex reporting queries

Proposed Syntax

Basic structure:

SELECT ...
FROM source_table
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN (pivot_value_list)
) AS alias

Example with static values:

SELECT *
FROM #separatedvalues.csv('sales.csv', true, 0)
PIVOT (
    Sum(Quantity)
    FOR Category IN ('Books', 'Electronics', 'Fashion')
) AS p

Example with dynamic pivot columns:

-- Let pivot columns be determined at runtime
WITH Categories AS (
    SELECT DISTINCT Category 
    FROM #separatedvalues.csv('sales.csv', true, 0)
)
SELECT *
FROM #separatedvalues.csv('sales.csv', true, 0)
PIVOT (
    Sum(Quantity)
    FOR Category IN (SELECT * FROM Categories)
) AS p

Key Design Decisions

1. Static vs Dynamic Pivoting

   • Option A: Only support static pivot values
     • Pros: Simpler implementation, predictable schema
     • Cons: Less flexible
   • Option B: Support both static and dynamic (preferred)
     • Pros: Maximum flexibility, handles real-world use cases
     • Cons: More complex implementation, requires CTE support

2. Type System Integration

   • Option A: Generate fixed result schema
     • Pros: Type-safe, predictable
     • Cons: Less dynamic
   • Option B: Dynamic schema generation (preferred)
     • Pros: Supports runtime pivoting
     • Cons: Requires careful type handling

3. Aggregation Support

   • Must support custom aggregations out of the box

Technical Considerations

graph TD
    A[Parser] -->|Add PIVOT syntax| B[Query Analyzer]
    B -->|Schema Resolution| C[Type System]
    C -->|Column Generation| D[Query Code Generator]
    D -->|Aggregation| E[Result Set]
    F[Plugin System] -->|Source Data| D

Loading

Key areas requiring changes:

1. Parser additions for PIVOT syntax
2. Query analyzer modifications for pivot validation
3. Dynamic schema generation system
4. Aggregation engine enhancements
5. Result set transformation logic

Example Use Cases

1. Sales Analysis

-- Monthly sales by product category
SELECT *
FROM #separatedvalues.csv('monthly_sales.csv', true, 0)
PIVOT (
    Sum(Revenue)
    FOR Month IN ('Jan', 'Feb', 'Mar')
) AS monthly_sales

2. Git Commit Analysis

-- Commits by author per month
SELECT *
FROM #git.repository('path/to/repo')
CROSS APPLY Commits
PIVOT (
    Count(Sha)
    FOR Month(CommittedWhen) IN (1,2,3,4,5,6,7,8,9,10,11,12)
) AS author_activity

3. System Resource Monitoring

-- Process CPU usage across time periods
SELECT *
FROM #os.processes()
PIVOT (
    Avg(CpuUsage)
    FOR TimeSlot IN ('Morning', 'Afternoon', 'Evening')
) AS resource_usage

Questions

1. Technical

   • Should it support multiple aggregate functions per pivot?

2. Usage

   • What's the most common pivot scenario in your workflows?
   • Do you need dynamic pivot column support?
   • Which aggregate functions are most important?

Next Steps

1. Phase 1: Static Pivoting

   • Basic PIVOT syntax implementation
   • Core aggregation functions
   • Parser/analyzer changes

2. Phase 2: Dynamic Pivoting

   • Dynamic column generation
   • Schema resolution
   • CTE integration