Paradime | PIVOT / UNPIVOT

SQL Keywords

PIVOT / UNPIVOT

Feb 23, 2026

min read

Category: Data Transformation

Platform Support:

✅ Snowflake (PIVOT) | ✅ BigQuery (PIVOT/UNPIVOT) | ✅ Databricks (PIVOT)

Description

PIVOT transforms rows into columns (wide format), while UNPIVOT transforms columns into rows (long format). Essential for reshaping data for analysis, reporting, and converting between normalized and denormalized formats.

Syntax

PIVOT:

SELECT *
FROM source_table
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN (value1, value2, ...)
)

SELECT *
FROM source_table
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN (value1, value2, ...)
)

SELECT *
FROM source_table
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN (value1, value2, ...)
)

UNPIVOT:

SELECT *
FROM source_table
UNPIVOT (
    value_column
    FOR name_column IN (column1, column2, ...)
)

SELECT *
FROM source_table
UNPIVOT (
    value_column
    FOR name_column IN (column1, column2, ...)
)

SELECT *
FROM source_table
UNPIVOT (
    value_column
    FOR name_column IN (column1, column2, ...)
)

Platform-Specific Notes

Snowflake:

Full PIVOT support
Dynamic PIVOT with variables
UNPIVOT support
Can pivot on multiple columns

BigQuery:

PIVOT and UNPIVOT supported
Requires explicit column list in PIVOT
String aggregation works well with PIVOT
UNPIVOT for converting wide to long

Databricks:

PIVOT supported (Spark SQL)
Can use expressions in pivot values
UNPIVOT via STACK or melt operations
Dynamic pivoting with SQL generation

Example 1: PIVOT - Sales by Month

All Platforms:

-- Before PIVOT: Long format
SELECT 
    product_name,
    month,
    SUM(sales) as monthly_sales
FROM sales_data
GROUP BY product_name, month;

-- With PIVOT: Wide format
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun')
) AS pivoted_sales
ORDER BY

-- Before PIVOT: Long format
SELECT 
    product_name,
    month,
    SUM(sales) as monthly_sales
FROM sales_data
GROUP BY product_name, month;

-- With PIVOT: Wide format
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun')
) AS pivoted_sales
ORDER BY

-- Before PIVOT: Long format
SELECT 
    product_name,
    month,
    SUM(sales) as monthly_sales
FROM sales_data
GROUP BY product_name, month;

-- With PIVOT: Wide format
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun')
) AS pivoted_sales
ORDER BY

Sample Data (sales_data - long format):

product_name	month	sales
Laptop	Jan	10000
Laptop	Feb	12000
Laptop	Mar	11000
Mouse	Jan	500
Mouse	Feb	600
Mouse	Mar	550

Result (wide format):

product_name	Jan	Feb	Mar	Apr	May	Jun
Laptop	10000	12000	11000	NULL	NULL	NULL
Mouse	500	600	550	NULL	NULL	NULL

Example 2: PIVOT - Multiple Aggregations

Snowflake/Databricks:

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
) AS pivoted
ORDER BY

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
) AS pivoted
ORDER BY

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
) AS pivoted
ORDER BY

BigQuery:

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
)
ORDER BY

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
)
ORDER BY

SELECT *
FROM (
    SELECT region, product_category, revenue, units_sold
    FROM regional_sales
)
PIVOT (
    SUM(revenue) as total_revenue,
    SUM(units_sold) as total_units
    FOR region IN ('East', 'West', 'North', 'South')
)
ORDER BY

Sample Data:

region	product_category	revenue	units_sold
East	Electronics	50000	150
West	Electronics	60000	180
East	Furniture	30000	75
West	Furniture	35000	90

Result:

product_category	East_total_revenue	East_total_units	West_total_revenue	West_total_units	...
Electronics	50000	150	60000	180	...
Furniture	30000	75	35000	90	...

Example 3: UNPIVOT - Convert Wide to Long

Snowflake:

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

BigQuery:

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
)
ORDER BY product_name, month

Databricks (using STACK):

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
LATERAL VIEW STACK(6,
    'jan', jan,
    'feb', feb,
    'mar', mar,
    'apr', apr,
    'may', may,
    'jun', jun
) AS month, sales
WHERE sales IS NOT NULL
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
LATERAL VIEW STACK(6,
    'jan', jan,
    'feb', feb,
    'mar', mar,
    'apr', apr,
    'may', may,
    'jun', jun
) AS month, sales
WHERE sales IS NOT NULL
ORDER BY product_name, month

SELECT 
    product_name,
    month,
    sales
FROM monthly_sales
LATERAL VIEW STACK(6,
    'jan', jan,
    'feb', feb,
    'mar', mar,
    'apr', apr,
    'may', may,
    'jun', jun
) AS month, sales
WHERE sales IS NOT NULL
ORDER BY product_name, month

Sample Data (monthly_sales - wide format):

product_name	jan	feb	mar	apr	may	jun
Laptop	10000	12000	11000	NULL	NULL	NULL
Mouse	500	600	550	NULL	NULL	NULL

Result (long format):

product_name	month	sales
Laptop	jan	10000
Laptop	feb	12000
Laptop	mar	11000
Mouse	jan	500
Mouse	feb	600
Mouse	mar	550

Example 4: Dynamic PIVOT (Snowflake)

Snowflake:

-- Create dynamic pivot query
SET pivot_values = (
    SELECT LISTAGG(DISTINCT '\'' || month || '\'', ', ') WITHIN GROUP (ORDER BY month)
    FROM sales_data
);

-- Execute dynamic pivot
EXECUTE IMMEDIATE '
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN (' || $pivot_values || ')
)
ORDER BY product_name;'

-- Create dynamic pivot query
SET pivot_values = (
    SELECT LISTAGG(DISTINCT '\'' || month || '\'', ', ') WITHIN GROUP (ORDER BY month)
    FROM sales_data
);

-- Execute dynamic pivot
EXECUTE IMMEDIATE '
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN (' || $pivot_values || ')
)
ORDER BY product_name;'

-- Create dynamic pivot query
SET pivot_values = (
    SELECT LISTAGG(DISTINCT '\'' || month || '\'', ', ') WITHIN GROUP (ORDER BY month)
    FROM sales_data
);

-- Execute dynamic pivot
EXECUTE IMMEDIATE '
SELECT *
FROM (
    SELECT product_name, month, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR month IN (' || $pivot_values || ')
)
ORDER BY product_name;'

Example 5: PIVOT for Cross-Tabulation

All Platforms:

-- Customer segmentation cross-tab
SELECT *
FROM (
    SELECT 
        age_group,
        income_level,
        customer_id
    FROM customers
)
PIVOT (
    COUNT(customer_id)
    FOR income_level IN ('Low', 'Medium', 'High', 'Very High')
) AS customer_matrix
ORDER BY

-- Customer segmentation cross-tab
SELECT *
FROM (
    SELECT 
        age_group,
        income_level,
        customer_id
    FROM customers
)
PIVOT (
    COUNT(customer_id)
    FOR income_level IN ('Low', 'Medium', 'High', 'Very High')
) AS customer_matrix
ORDER BY

-- Customer segmentation cross-tab
SELECT *
FROM (
    SELECT 
        age_group,
        income_level,
        customer_id
    FROM customers
)
PIVOT (
    COUNT(customer_id)
    FOR income_level IN ('Low', 'Medium', 'High', 'Very High')
) AS customer_matrix
ORDER BY

Sample Data:

customer_id	age_group	income_level
1	18-25	Low
2	18-25	Medium
3	18-25	Low
4	26-35	High
5	26-35	Medium
6	36-50	Very High

Result:

age_group	Low	Medium	High	Very High
18-25	2	1	0	0
26-35	0	1	1	0
36-50	0	0	0	1

Example 6: PIVOT with Conditional Aggregation Alternative

All Platforms (without PIVOT):

-- Alternative to PIVOT using CASE statements
SELECT 
    product_name,
    SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) as jan,
    SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) as feb,
    SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) as mar,
    SUM(CASE WHEN month = 'Apr' THEN sales ELSE 0 END) as apr,
    SUM(CASE WHEN month = 'May' THEN sales ELSE 0 END) as may,
    SUM(CASE WHEN month = 'Jun' THEN sales ELSE 0 END) as jun
FROM sales_data
GROUP BY product_name
ORDER BY

-- Alternative to PIVOT using CASE statements
SELECT 
    product_name,
    SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) as jan,
    SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) as feb,
    SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) as mar,
    SUM(CASE WHEN month = 'Apr' THEN sales ELSE 0 END) as apr,
    SUM(CASE WHEN month = 'May' THEN sales ELSE 0 END) as may,
    SUM(CASE WHEN month = 'Jun' THEN sales ELSE 0 END) as jun
FROM sales_data
GROUP BY product_name
ORDER BY

-- Alternative to PIVOT using CASE statements
SELECT 
    product_name,
    SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) as jan,
    SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) as feb,
    SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) as mar,
    SUM(CASE WHEN month = 'Apr' THEN sales ELSE 0 END) as apr,
    SUM(CASE WHEN month = 'May' THEN sales ELSE 0 END) as may,
    SUM(CASE WHEN month = 'Jun' THEN sales ELSE 0 END) as jun
FROM sales_data
GROUP BY product_name
ORDER BY

This produces the same result as PIVOT but is more verbose

PIVOT vs Manual Aggregation

Aspect	PIVOT	CASE WHEN
Readability	High	Medium
Dynamic columns	Harder	Harder
Performance	Optimized	Similar
Flexibility	Limited	High
Syntax	Cleaner	Verbose

Common Use Cases

PIVOT:

Time-series analysis: Sales by month, year
Cross-tabulation: Demographics vs behavior
Reporting: Convert transactional to summary format
Dashboards: Prepare data for visualization
Comparison: Side-by-side metrics

UNPIVOT:

Normalization: Convert wide tables to long
Data import: Flatten spreadsheet data
Time-series: Monthly columns to date rows
Analytics: Prepare data for aggregation
ETL: Transform legacy formats

Performance Considerations

PIVOT can be memory-intensive with many columns
UNPIVOT is generally efficient
Consider materialized views for frequent pivots
Index base columns before pivoting
Limit pivot columns when possible

Best Practices

Know your pivot values: List all expected values
Handle NULLs: Use COALESCE or IFNULL for missing data
Limit columns: Too many pivot columns hurt readability
Document transformations: Explain pivot logic
Consider alternatives: CASE WHEN for simple pivots
Test with real data: Verify all expected values appear
Use aliases: Make column names readable

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources