INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
By Emma Tsai
Performing functional testing for software applications is part of the job as a business analyst. Occasionally, I need to work with SQL server to do database queries so that I can verify that the application pulls data results as expected. Recently, I learned how to use PIVOT operator to group and aggregate data in SQL database. Here is how it works:
In pivot queries, here are the three elements you need to identify first:
1. What do you want to see on rows? Also known as grouping elements
2. What do you want to see on columns? Also known as spreading elements
3. What do you want to see in the intersection of each distinct row and column? Also known as aggregation elements
The following example assumes that you want to query the Sales.Orders table. You want to return a row for each distinct customer (grouping element), a column for each distinct shipper (spreading element), in this case [1], [2], [3], and the sum of freight value (aggregation element).
I already defined a table expression (like the one named PivotData) that returns the three elements needed in pivoting instead of querying the underlying source table directly. In the specification of a PIVOT operator, the grouping element is identified by elimination, which is what is left from the queried table besides the spreading and aggregation elements. If you query the underlying table directly (Sales.Orders in this case), all columns from the table, except for the freight and shipperid columns, will implicitly be your grouping elements. Instead of getting a row per customer, you got a row per order, which returns 830 rows back (since there are 830 rows in the Sales.Orders table).
I hope this tip can help you pivot data more efficiently.
Reference: Exam 70-461 Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage