Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 FOR clause correctly, which defines the column holding original column names.
  • Including columns in the IN list 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

ClauseDescription
UNPIVOTClause to convert columns into rows
value_columnName of the new column holding unpivoted values
FOR unpivot_columnName 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.