For an instructor lead, in-depth look at learning SQL click below.
Structured Query Language, or SQL, offers a multitude of operators to refine and manipulate data stored in relational databases. Among these operators, UNPIVOT holds a lot of significance. The UNPIVOT operator is used to convert columns into rows. It is, in essence, the opposite of the PIVOT operation which is used to transform rows into columns, facilitating a more readable and understandable view of data.
When is the UNPIVOT operator used?
Unpivot is particularly useful when it’s necessary to normalize data from a denormalized database schema. UNPIVOT provide a significant advantage while working with certain kind of reports and it can restructure the data in a way easier to analyze.
How does the UNPIVOT operator work in SQL?
Essentially, UNPIVOT performs almost a reverse of a PIVOT operation. It converts individual column data into rows. Let’s take an example to understand this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE Temp ( Product VARCHAR(10), Qtr1 INT, Qtr2 INT, Qtr3 INT, Qtr4 INT ); INSERT INTO Temp VALUES ('Widget', 25, 32, 24, 30); SELECT Product, Quarter, Sales FROM (SELECT Product, Qtr1, Qtr2, Qtr3, Qtr4 FROM Temp) t UNPIVOT (Sales FOR Quarter IN (Qtr1, Qtr2, Qtr3, Qtr4) )AS unpvt; |
In the above example, we first create a temporary table and populate it with sales data for some ‘Widget’ for different quarters. Now, if we apply the above UNPIVOT query, it will output:
|
1 2 3 4 5 6 7 8 |
Product | Quarter | Sales ---------------------------- Widget | Qtr1 | 25 Widget | Qtr2 | 32 Widget | Qtr3 | 24 Widget | Qtr4 | 30 |
The query essentially converted the quarterly columns into rows with corresponding sales data.
Conclusion
In summation, the UNPIVOT operator in SQL adds versatility to how you can view, analyze, and manipulate your data structures. Its primary purpose is to turn columns into rows, a useful feature for many forms of data analysis and restructuring.