microsoft sql server 2005 implementation and maintenance - 217
C0562271X.fm Page 179 Friday, April 29, 2005 7:32 PM
Lesson 1: Querying Data
179
The following query shows how to produce the average number of vacation hours for all salaried and nonsalaried employees in the Employee table in a single output row:
SELECT [0], [1] FROM ( SELECT SalariedFlag, VacationHours FROM HumanResources.Employee ) AS H PIVOT ( AVG(VacationHours) FOR SalariedFlag IN ([0], [1]) ) AS Pvt
In this example, the data from the Employee table is first selected in the derived table called H. The data from the table is pivoted using the AVG aggregate to produce two columns—0 and 1—each corresponding to one of the two salary types in the Employee table. Note that the same identifiers used to define the pivot columns must also be used in the SELECT list if you want to return the columns’ values to the user. The UNPIVOT operator does the exact opposite of the PIVOT operator. It turns columns back into rows. This operator is useful when you are normalizing tables that have more than one column of the same type defined.
Creating Queries That Use Full-Text Search
If your database contains many columns that use string data types such as VARCHAR or NVARCHAR, you might find that searching these columns for data by using the Transact-SQL = and LIKE operators does not perform well. A more efficient way to search text data is to use the SQL Server FTS capabilities. To do full-text searching, you first must enable full-text indexes for the tables you want to query. To query a full-text index, you use a special set of functions that differ from the operators that you use to search other types of data. The main functions for full-text search are CONTAINS and FREETEXT. The CONTAINS function searches for exact word matches and word prefix matches. For instance, the following query can be used to search for any address containing the word “Stone”:
SELECT * FROM Person.Address WHERE CONTAINS(AddressLine1, 'Stone')
