In SQL, the UNPIVOT operator is used to convert columns of a table into rows. This is particularly useful when you have a dataset where data is spread across multiple columns, and you want to reorganize it into a more normalized format.
Basic Syntax
The general syntax for using UNPIVOT is as follows:
SELECT <columns>
FROM
(SELECT <columns>
FROM <your_table>) AS SourceTable
UNPIVOT
(<new_column_name> FOR <old_column_name> IN (<list_of_columns>)) AS UnpivotedTable;
Explanation of the Syntax
- <columns>: The columns you want to select from the unpivoted table.
- <your_table>: The original table name.
- <new_column_name>: The name you want to give to the new column that will hold the unpivoted values.
- <old_column_name>: The name you want to give to the new column that will indicate the original column names.
- <list_of_columns>: The list of columns from the original table that you want to convert into rows.
Example
Let's say you have a table named Sales with the following structure:
| Year | Q1 | Q2 | Q3 | Q4 |
|------|----|----|----|----|
| 2022 | 100| 150| 200| 250|
| 2023 | 120| 180| 220| 300|
You want to convert the quarters (Q1, Q2, Q3, Q4) into rows.
Here’s an example of how to use UNPIVOT:
SELECT Year, Quarter, SalesAmount
FROM
(SELECT Year, Q1, Q2, Q3, Q4
FROM Sales) AS SourceTable
UNPIVOT
(SalesAmount FOR Quarter IN (Q1, Q2, Q3, Q4)) AS UnpivotedTable;
Output
The output of the above query would be:
| Year | Quarter | SalesAmount |
|------|---------|-------------|
| 2022 | Q1 | 100 |
| 2022 | Q2 | 150 |
| 2022 | Q3 | 200 |
| 2022 | Q4 | 250 |
| 2023 | Q1 | 120 |
| 2023 | Q2 | 180 |
| 2023 | Q3 | 220 |
| 2023 | Q4 | 300 |
Important Notes
- The UNPIVOT operator may not be available in some SQL database systems (like MySQL). In such cases, equivalent logic can often be achieved using UNION ALL.
- Ensure the columns being unpivoted are of a compatible data type.
- The performance of unpivoting can vary depending on the data size and the database optimization.
This should give you a clear understanding of how to utilize the UNPIVOT operator in SQL to convert columns into rows!