SQL Server Techniques for Creating a Web Reporting Page — Part 1

The magic in our new groupSales code block is the use of a derived table, CASE and PATINDEX. For example, when we group by region, SQL will return a row for every region. But if there was only one region, SQL will only return one row. This is what the derived table does. If the user doesn’t want to group by region, the derived table changes all regions to ‘’. The outer group by statement returns one row. The derived table performs its magic by using the case statement. If the @group parameter has the word “region”, then don’t alter region, else change it to ‘’. The case statement uses PATINDEX to search for the word “region” in the @group parameter. If it finds it, it returns the starting character position else it returns zero.

Here are some results:

exec CubeSp @group=’all’


0 138

exec CubeSp @group=’region’


East 0 4

North 0 24

South 0 46

West 0 64

exec CubeSp @group=’region,empid’


North 1 18

North 2 6

South 12 44

South 13 2

East 21 4

West 31 24

West 32 36

West 33 4

exec CubeSp @group=’region,empid’,@region=’north’


North 1 18

North 2 6

This concludes our article about SEARCH & GROUP. In the future, I hope to write articles about implementing SORT, TOTAL ROWS, and PIVOT/TRANSPOSE functionality via SQL Server.

About the Author

Louis Duc Nguyen lives in Dallas, TX and has a BS in Engineering and an MBA from University of Texas at Austin and Dallas. He is currently working for a major airline. He specializes in creating data-entry and reporting applications. Please address comments and hot job tips to Louis Nguyen at louisducnguyen@hotmail.com.

Copyright © 2002-2003 Louis Duc Nguyen All Rights Reserved


Pages: 1 2 3


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |