How to Use UNPIVOT in Snowflake: Syntax and Examples
In Snowflake, use the
UNPIVOT clause to convert columns into rows by specifying the columns to unpivot and the output column names. This helps reshape wide tables into a normalized, long format for easier analysis.Syntax
The UNPIVOT syntax in Snowflake transforms columns into rows. You specify the output column for values and the columns to unpivot. Optionally, you can rename the column that holds the original column names.
- value_column: The name of the new column that will hold the unpivoted values.
- unpivot_column: The name of the new column that will hold the original column names.
- columns_to_unpivot: The list of columns you want to convert into rows.
sql
SELECT * FROM your_table UNPIVOT( value_column FOR unpivot_column IN (column1, column2, column3) );
Example
This example shows how to unpivot sales data from columns representing months into rows with month names and sales values.
sql
WITH sales_data AS ( SELECT 1 AS id, 100 AS Jan, 150 AS Feb, 200 AS Mar UNION ALL SELECT 2, 120, 180, 210 ) SELECT id, month, sales FROM sales_data UNPIVOT( sales FOR month IN (Jan, Feb, Mar) ) ORDER BY id, month;
Output
ID | MONTH | SALES
---|-------|------
1 | Jan | 100
1 | Feb | 150
1 | Mar | 200
2 | Jan | 120
2 | Feb | 180
2 | Mar | 210
Common Pitfalls
Common mistakes when using UNPIVOT include:
- Not specifying the
FORclause correctly, which defines the column holding original column names. - Including columns in the
INlist that do not exist or have incompatible data types. - Forgetting to alias the output columns clearly, causing confusion in results.
Always check that the columns you unpivot have compatible data types.
sql
/* Wrong: Missing FOR clause */ SELECT * FROM sales_data UNPIVOT( sales IN (Jan, Feb, Mar) ); /* Right: Correct FOR clause usage */ SELECT * FROM sales_data UNPIVOT( sales FOR month IN (Jan, Feb, Mar) );
Quick Reference
| Clause | Description |
|---|---|
| UNPIVOT | Clause to convert columns into rows |
| value_column | Name of the new column holding unpivoted values |
| FOR unpivot_column | Name of the new column holding original column names |
| IN (columns) | List of columns to unpivot |
Key Takeaways
Use UNPIVOT to turn multiple columns into rows for easier data analysis.
Always specify the FOR clause to name the column holding original column names.
Ensure columns in the IN list exist and have compatible data types.
Alias output columns clearly to avoid confusion in results.
