In SQL Server, the UNPIVOT operator is used to transform columns into rows, allowing you to convert a dataframe-like structure where multiple columns represent similar types of data into a more normalized form. This can be particularly useful for reporting and analysis.
Syntax of UNPIVOT
The basic syntax for using UNPIVOT is as follows:
SELECT [column1], [column2], [column_value]
FROM
(SELECT [column1], [column2], [pivot_column1], [pivot_column2], ...
FROM your_table_name) AS SourceTable
UNPIVOT
([column_value] FOR [pivot_column] IN ([pivot_column1], [pivot_column2], ...)) AS UnpivotedTable;
Example Scenario
Let's say you have a table named SalesData that tracks sales for different months:
| Product | Jan_Sales | Feb_Sales | Mar_Sales |
|---------|-----------|-----------|-----------|
| A | 100 | 150 | 200 |
| B | 200 | 250 | 300 |
| C | 150 | 100 | 50 |
You want to transform this table so that each month's sales data is presented in rows instead of columns.
Using UNPIVOT
Here is how you can utilize the UNPIVOT operator to accomplish this transformation:
SELECT Product, Month, Sales
FROM
(SELECT Product, Jan_Sales, Feb_Sales, Mar_Sales
FROM SalesData) AS SourceTable
UNPIVOT
(Sales FOR Month IN (Jan_Sales, Feb_Sales, Mar_Sales)) AS UnpivotedTable;
Explanation
1. Inner Query (SourceTable): This selects the required columns from the SalesData table. This is necessary because UNPIVOT operates on a derived table.
2. UNPIVOT Operation:
- Sales FOR Month IN (Jan_Sales, Feb_Sales, Mar_Sales): This specifies that Sales will contain the values from the specified columns, and the values in the Month column will identify which original sales column they originated from.
Result
After running the above query, you would get the following result:
| Product | Month | Sales |
|---------|------------|-------|
| A | Jan_Sales | 100 |
| A | Feb_Sales | 150 |
| A | Mar_Sales | 200 |
| B | Jan_Sales | 200 |
| B | Feb_Sales | 250 |
| B | Mar_Sales | 300 |
| C | Jan_Sales | 150 |
| C | Feb_Sales | 100 |
| C | Mar_Sales | 50 |
Notes
- Ensure that you specify the correct data type for the columns being unpivoted, as the output will inherit the data types of the original columns.
- When using UNPIVOT, the original column names will be represented in a single column, which might be denormalized for some operations, but it provides a straightforward layout for analytical tasks.
- You might want to apply additional transformations using JOINs or other SQL functionality if you need further processing of the unpivoted data.