Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Chapter 11. Advanced Capabilities

This chapter introduces you to a variety of technologies and features that go beyond standard queries and simple data manipulation. First, you learn to use the new PIVOT and UNPIVOT operators to rotate and rearrange denormalized data structures for reporting and summarization. The next section explores a variety of tools designed to work with text matching and indexing large text fields. These include using the SOUNDEX and DIFFERENCE functions to compare text and to perform approximate phonetic matching. You will learn to use full-text indexes and a collection of functions specifically designed to find patterns and similarities between large text fields, comments, and notes stored in a database.

Pivoting Data

I have to say that this is one of the more interesting newer features in SQL Server. It is interesting for a variety of reasons. Back in 1970, when E. F. Codd first introduced the principles of relational database design in his paper, "A Relational Model of Data for Large Shared Data Banks," for the Association of Computing Machinery, his principles became the foundation on which many of today's fundamental, industry-wide database design patterns are based. The cornerstone of these paramount design rules is the first rule of normal form, which states that an entity shouldn't contain duplicate types of attributes. This means that a table shouldn't have more than one column that represents the same type of non-distinct value; or, in other words, ...