surface↓pipeline↓pivot
Pipelinepipeline · returns table

PIVOT

Transform rows to columns with smart pivot/cross-tabulation

Table-in, table-out — composes downstream of SELECTs.

pipelinellmpipeline-composabletext

Syntax

THEN PIVOT {{ prompt }}
THEN PIVOT({{ prompt }})

Arguments

nametypedescription
promptVARCHAR—
_tableTABLE—

About

PIPELINE cascade for smart pivoting (cross-tabulation) of query results. SQL PIVOT syntax is notoriously painful - different across databases, no dynamic pivots in most dialects, and confusing syntax. This operator makes pivoting easy with natural language or simple parameters. Uses structural caching via table_sql_execute mode: - LLM only sees table SCHEMA (column names + types), not actual data - Generates native DuckDB PIVOT SQL - SQL cached by schema fingerprint + prompt - Same table shape + same prompt = instant cache hit Usage examples: SELECT * FROM sales THEN PIVOT 'revenue by region for each product' SELECT * FROM data THEN PIVOT('category', 'amount') SELECT * FROM data THEN PIVOT 'with subtotals'

Examples

Crosstabulates region values into pivoted revenue columns

SELECT
  *
FROM
  (
    VALUES
      ('Widget', 'North', 10),
      ('Widget', 'South', 20),
      ('Gadget', 'North', 5),
      ('Gadget', 'South', 8)
  ) AS t (product, region, revenue) THEN PIVOT ('revenue by region for each product') THEN PYTHON (
    'result = pd.DataFrame({"ok":[len(df) in (2, 3) and "North" in df.columns and "South" in df.columns and int(df.loc[df["product"] == "Widget", "North"].iloc[0]) == 10]})'
  )

Nearby rabbit holes

same domain
pipeline

ADD_STYLES

Apply theme styling to a chart specification

pipelinellmpipeline-composabletext
pipeline

ANALYZE

Analyze query results with LLM based on a prompt

pipelinellmpipeline-composabletext
pipeline

DEDUPE

Remove duplicate rows

pipelinellmpipeline-composabletext
pipeline

ENRICH

Add LLM-computed columns to query results

pipelinellmpipeline-composabletext
pipeline

FILTER

Filter query results using LLM-based semantic matching

pipelinellmpipeline-composabletext
pipeline

GROUP

Group by column and aggregate another

pipelinellmpipeline-composabletext
Climb back to The Looking Glass