Cross tab queries with SQL Server 2000

By: Greg Robidoux   |   Read Comments   |   Related Tips: 1 | 2 | More > Scripts

Problem
In SQL Server 2000 there is no simple way to create a cross tab query.  Generally crosstab queries are only used for reporting or if you want to display some information in a grid on an application screen.  Other then displaying or reporting on data there isn't a real need for this type of query.  But when the need arises for either one of these reasons, there is no simple way of doing this in SQL Server 2000.  This may come as a big surprise to people that have been doing this in Microsoft Access for quite some time. With Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000.

Solution
As mentioned above there is no simple way to produce a cross tab query, but several implementations have been produced and are available on the internet.  Finding all of these options, understanding them, implementing and testing takes some time, so to make your life a little easier we have gathered a list of some useful variations on how to produce cross tab queries in SQL Server 2000.

The following solution is pretty straight forward and offers a lot of flexibility.  Although it met my needs, take a look at the other options as well to see which one makes the most sense for your needs.

• http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

This is a list of various implementations and methods for creating crosstab queries for SQL Server 2000. There are probably others out there as well.

• http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
• http://www.sqlteam.com/item.asp?ItemID=2955
• http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html
• http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx
• http://www.tek-tips.com/faqs.cfm?fid=5278
• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp
• http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

Some other options that do not require T-SQL coding include the following:

• GeckoWare SQL Crosstab Builder for Microsoft SQL Server
• Rac for SQL2K
• Matrix reports in SQL Server Reporting Services

Next Steps

• Take a look at these various methods for creating crosstab queries
• Select a method that works best for your environment
• Modify these methods to implement the right solution for your needs

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• Crosstab queries using PIVOT in SQL Server...
• More Database Developer Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code