Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 239

Conclusion

This chapter covered window functions, pivoting and unpivoting data, and features related to group-

ing sets. 

Window functions allow you to perform calculations against sets in a more flexible and efficient 

manner when compared to alternative methods. Window functions have numerous practical uses, so 

it’s well worth your time to get to know them well.

I provided both standard and nonstandard techniques to achieve pivoting and unpivoting. The 

nonstandard techniques use the T-SQL–specific PIVOT and UNPIVOT operators; the main advantage 

of these is that they require less code than standard techniques.

SQL Server supports several important features that make the handling of grouping sets flex-

ible and efficient: the GROUPING SETS, CUBE, and ROLLUP subclauses and the GROUPING and 

GROUPING_ID function. 

Exercises

This section provides exercises to help you familiarize yourself with the subjects discussed in Chapter 7. 

All exercises for this chapter involve querying the dbo.Orders table in the TSQL2012 database that you 

created and populated earlier in this chapter by running the code in Listing 7-1.

1

Write a query against the dbo.Orders table that computes for each customer order both a rank and a 

dense rank, partitioned by custid and ordered by qty.

■

■

Tables involved: TSQL2012 database and dbo.Orders table

■

■

Desired output:

custid orderid     qty         rnk                  drnk 

------ ----------- ----------- -------------------- -------------------- 

A      30001       10          1                    1 

A      40005       10          1                    1 

A      10001       12          3                    2 

A      40001       40          4                    3 

B      20001       12          1                    1 

B      30003       15          2                    2 

B      10005       20          3                    3 

C      10006       14          1                    1 

C      20002       20          2                    2 

C      30004       22          3                    3 

D      30007       30          1                    1

www.it-ebooks.info

---