Unpivot, a SQL transformation function, plays a pivotal role in data manipulation, especially when dealing with contextual data. It excelente at converting columns into rows and vice versa, simplifying data analysis and offering more flexibility in querying. Let's delve into an SQL unpivot example to grasp its functionality better.
Before diving into the details, understand the basic syntax of unpivot: PREPIVOT(column Expression) IN ( column1 ... ) WHERE condition ORDER BY column1, ...
Understanding SQL Unpivot: A Basic Example
Imagine a scenario where you have a table, Emp, with Employee ID (EID), Name, and three fields representing three different months' salaries: Jan, Feb, and March. You'd like to see each employee's salary for each month instead of three different columns.
Here's how you'd use unpivot to achieve this:
SELECT EID, Name, Sal_Month, Salary
FROM Emp
UNPIVOT (Salary FOR Sal_Month IN (Jan, Feb, March)) AS unpivoted
Explaining the Above SQL Unpivot Example
The UNPIVOT clause transforms the three columns (Jan, Feb, Mar) to rows. The syntax UNPIVOT (Salary FOR Sal_Month IN (...)) AS unpivoted hoists the value from each of the columns to a row under the Salary column and changes the column name to Sal_Month.
The AS keyword is then used to define an alias, "unpivoted", to the resultant table consisting of the row-transformed data.
The Power of SQL Unpivot: Pivot and Unpivot Together
Unpivot isn't the only transformation function at your disposal in SQL. It pairs excellently with pivot, which moves data from rows to columns. Together, they're exceptionally powerful in data manipulation and rolls out handy in real-life data analytics.
Say, you have a similar table, Sales, with Product_ID, Month, and Sales. The Sales are scattered across multiple rows, one for each month. To have a single row representing total sales by product, use the following SQL code, combining UNPIVOT and PIVOT:
SQL Unpivot & Pivot Example: Transforming Rows to Columns
Here's how to pivot the data from rows to columns – aggregating sales by product for each month.
SELECT Product_ID, SUM(Sales) as TotalSales
FROM Sales
PIVOT(SUM(Sales) FOR Month IN ([Jan], [Feb], [Mar])) AS pivoted
Remember, you can switch between pivot and unpivot based on your data requirements. Both transform data efficiently, making it easier to analyze, visualize, and draw meaningful insights.
SQL's unpivot function may pose some initial learning curve, but once mastered, it unveils a world of opportunities, especially when handling contextual data. Practice with SQL unpivot examples to foster your skills and dabble in more complex transformation tasks.