The SQL `UNPIVOT` function, introduced in SQL Server 2005 and later adopted by other database systems, is a powerful tool for converting rows to columns or vice versa. It's particularly useful when you have data stored in a row format, but you need to display it in a column format, or when you need to transpose data from a ResultSet. This article will guide you through the basics of `UNPIVOT` using examples from W3Schools, ensuring you grasp this essential skill.
Before we dive into the examples, let's understand the basic syntax of `UNPIVOT`:
Understanding the UNPIVOT Syntax
The basic syntax of `UNPIVOT` looks like this:
SELECT * FROM
`UNPIVOT` operates on the columns listed in the IN clause. It uses the column specified in the FOR clause to hold the original column name as a new column in the result set.
Example 1: Converting Rows to Columns
Let's start with an example from W3Schools that converts rows to columns. Consider a `Customer` table with the following data:
CustomerID | Product | Quantity | Price
1 | ProductA | 10 | 10.00
1 | ProductB | 20 | 20.00
Now, we want to convert this to a format that displays each product as a separate column:
SELECT CustomerID, Quantity, Price, UnitPrice FROM Customer UNPIVOT (UnitPrice FOR Product IN (ProductA, ProductB))
The result would be:
CustomerID | Quantity | Price | ProductA | ProductB
1 | 30 | 30.00 | 10.00 | 20.00
The `UNPIVOT` function has effectively turned the rows into columns, placing the product names as column headers.
Example 2: Using UNPIVOT with Dynamic SQL
Suppose we have a `OrderDetails` table, and we want to convert the columns containing product names into rows. However, the table might have a varying number of product columns. In such cases, we can use dynamic SQL to generate the `UNPIVOT` query:
First, let's create our table:
CREATE TABLE OrderDetails (OrderID INT, OrderDate DATE, Product1 VARCHAR(10), Quantity1 INT, Price1 DECIMAL(5,2), Product2 VARCHAR(10), Quantity2 INT, Price2 DECIMAL(5,2), ...)
Now, let's create dynamic SQL to unpivot the product columns:
DECLARE @columns AS NVARCHAR(MAX) = '[' + STR(NUM) + ']'
DECLARE @query AS NVARCHAR(MAX) = 'SELECT OrderID, OrderDate, ''Product0'', MAX(Quantity) OVER (PARTITION BY OrderID, Product0) AS Quantity, MAX(Price) OVER (PARTITION BY OrderID, Product0) AS Price FROM OrderDetails UNPIVOT (''' + @columns + ''' FOR Product IN (Product1, Product2, ...)) AS resultAlias'
EXEC (@query)
The result will be a table with only four columns: `OrderID`, `OrderDate`, `Product0`, `Quantity`, and `Price`, regardless of the number of products in the original table.
More Applications of UNPIVOT
Aside from the examples above, `UNPIVOT` is also useful in many other scenarios. It can help convert data from and to a cross-tab format, perform roll-ups and aggregations on rows or columns, and more.
Applying UNPIVOT for Pivot Tables
`UNPIVOT` can be used to create pivot tables, allowing you to switch the rows and columns around to gain different insights from your data.
UNPIVOT and Dynamic Pivot Tables
Combining `UNPIVOT` with some dynamic SQL magic, you can create dynamic pivot tables that automatically adjust to the data they're working with.
Remember, `UNPIVOT` is a powerful tool, but like all tools, it's essential to use it correctly and understand its limitations. It can greatly simplify complex data transformations, so don't hesitate to explore its capabilities further!
Happy coding, and until next time, keep learning and experimenting with your SQL skills!