Pivoting and Unpivoting Data AI Prompt

Write SQL to pivot rows to columns and unpivot columns to rows.

Source data: {{data_description}}
Desired output: {{desired_output}}
Database: {{database}}

1. Manual pivot with CASE WHEN (works in all databases):
   -- Pivot: rows of (product, month, revenue) → one column per month
   SELECT
     product,
     SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan_revenue,
     SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb_revenue,
     SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar_revenue
   FROM monthly_sales
   GROUP BY product;

2. CROSSTAB (PostgreSQL tablefunc extension):
   CREATE EXTENSION IF NOT EXISTS tablefunc;

   SELECT * FROM CROSSTAB(
     'SELECT product, month, revenue
      FROM monthly_sales
      ORDER BY 1, 2',
     'VALUES (''Jan''),(''Feb''),(''Mar'')'
   ) AS ct(product TEXT, jan NUMERIC, feb NUMERIC, mar NUMERIC);

3. Unpivot: columns to rows (PostgreSQL UNNEST approach):
   -- Transform: one row with jan_rev, feb_rev, mar_rev → 3 rows
   SELECT
     product,
     month_name,
     revenue
   FROM monthly_wide_table,
   LATERAL (
     VALUES
       ('Jan', jan_revenue),
       ('Feb', feb_revenue),
       ('Mar', mar_revenue)
   ) AS t(month_name, revenue);

4. Dynamic pivot (when column values are unknown at query time):
   In SQL this requires dynamic SQL or a two-step approach:
   Step 1: SELECT DISTINCT month FROM monthly_sales → get the list of columns
   Step 2: Build and execute dynamic SQL with EXECUTE in a PL/pgSQL function

5. BigQuery / Snowflake PIVOT syntax:
   -- BigQuery:
   SELECT * FROM monthly_sales
   PIVOT (SUM(revenue) FOR month IN ('Jan', 'Feb', 'Mar'));

   -- Snowflake:
   SELECT * FROM monthly_sales
   PIVOT (SUM(revenue) FOR month IN ('Jan', 'Feb', 'Mar'))
   AS p (product, jan, feb, mar);

Return: pivot SQL for the specific data, unpivot SQL if needed, and dynamic pivot approach if the column values are dynamic.