Summary
In this chapter, we learned how to use the PIVOT and UNPIVOT operators and how we can use them in our advanced query techniques by transforming rows into columns or vice versa. We looked at a few examples and also learned how to dynamically construct column names that are used in pivoting operations. Both of these operators are very powerful and are a very good and clean replacement for the lengthy CASE statement we studied in Chapter 4.
The PIVOT option is used a lot for reporting purposes and analyzing data in different formats and UNPIVOT is more for denormalizing datasets and storing them in SQL tables.
Next, we looked at the new way of storing hierarchical data, using the hierarchyid data type. We walked through a very typical use case of storing organization hierarchy data with examples.
In the next chapter, we will move our focus onto the security aspects of SQL queries and the things we need to take care of while querying data from a database, along with how...
