Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Cross tab queries with SQL Server 2000

By:   |   Last Updated: 2006-06-15   |   Comments (2)   |   Related Tips: 1 | 2 | 3 | More > Scripts


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.


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.

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.

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

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

Last Updated: 2006-06-15

get scripts

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, October 30, 2017 - 2:07:56 PM - Greg Robidoux Back To Top

Hi Elaine,

what version of SQL Server are you using? If you are using SQL 2005 or later you should look at using PIVOT.  Take a look at this other tip: https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/


Friday, October 20, 2017 - 4:46:16 PM - Elaine Filos Back To Top

 Can you help? This is the data I have and the code I am using.

Two tables with this code

Select t1. ID#, t2.CHarge_Code

from Table 1 t1

Left outer join Table 2 t2 on t2.ID = t1.ID

where t2.charge_codes in

('2512', '25006', '14226') 

This is the result:

1602383 25012  
1602383 25006  
I want to be able to displayall of  the charge codes data on one line.  The charge code field is not an array.
ID # Charge code 1 charge Code 2
1602383 25012 25006


Learn more about SQL Server tools