Solutions to Common Query Puzzles

When you work with data, some situations are easy to solve with a query, either simple or complex. Other times, solutions are not so clear. Among some of the more complicated solutions are:

• Cross-tab reports in which summary information is created. Most cross-tab reports can be generated by just using either the CASE function or the CUBE or ROLLUP options of SELECT.
• Expanding hierarchies in which the hierarchical steps of getting to a particular result must be shown, for example, tracing a genealogical family tree from a great-great-grandparent to yourself.
• Expanding networks in which the multiple hierarchical steps must be shown; for example, all flights originating from Seattle and landing in New York.

There are now many books available on SQL in general, and several books on Transact-SQL itself. These books have solutions to many more query problems.