The UNPIVOT operator in SQL is used to transform columns in a table into rows, effectively converting a wide format into a long format. This is particularly useful when you want to normalize your data or analyze it in a different context.
Here’s a general syntax for using the UNPIVOT operator:
SELECT <non-pivoted column(s)>, <pivoted columns> AS <alias>
FROM
(
SELECT <non-pivoted column(s)>, <pivoted column(s)>
FROM <table_name>
) AS source_table
UNPIVOT
(
<new_column_name> FOR <old_column_name> IN (<list_of_columns_to_unpivot>)
) AS unpivoted_table;
Explanation:
- <non-pivoted column(s)>: These are the columns that remain unchanged in the output.
- <pivoted columns>: These are the columns whose data you want to transform into rows.
- source_table: This is a derived table representing your original table structure.
- <new_column_name>: This is the name you want to give to the newly created column that will hold the values from the pivoted columns.
- FOR <old_column_name>: This specifies the name of the new column that will contain the names of the original columns.
- <list_of_columns_to_unpivot>: This is a list of the columns you wish to convert to rows.
Example:
Suppose you have a table called SalesData that looks like this:
| Product | Q1 | Q2 | Q3 |
|---------|----|----|----|
| A | 100| 150| 200|
| B | 200| 250| 300|
If you want to unpivot the quarterly sales into rows, you can use UNPIVOT like so:
SELECT Product, Quarter, Sales
FROM
(
SELECT Product, Q1, Q2, Q3
FROM SalesData
) AS SourceTable
UNPIVOT
(
Sales FOR Quarter IN (Q1, Q2, Q3)
) AS UnpivotedTable;
Result:
The result would look like this:
| Product | Quarter | Sales |
|---------|---------|-------|
| A | Q1 | 100 |
| A | Q2 | 150 |
| A | Q3 | 200 |
| B | Q1 | 200 |
| B | Q2 | 250 |
| B | Q3 | 300 |
Important Notes:
- The UNPIVOT operation is supported in SQL Server and some other RDBMS systems; make sure to check the specific documentation for your SQL dialect.
- The UNPIVOT operator is usually used within a derived table or a Common Table Expression (CTE) so that you can select from it afterward.
- The columns that are being unpivoted must be of the same data type.