Currently at work,  we are re-writing an Access project (.mdb) to an ADP.  As we battle through the process, we encountered something that's not 'natively' supported in SQL Server...crosstab queries.

So, I took some time to research this problem and found out some pretty good resources on achieving 'crosstab-ness' using T-SQL.  The first method is by far the best method...since it's the most mathematically based.  It's called the Rozenshtein Method developed by David Rozenshtein, a Russian mathematician.

This method uses the concept of Boolean aggregates so that each column has a numeric expression that resolves each row as a zero or one and that value (0 or 1) is multiplied by your numeric expression.  To read more about this process visit Stephen Forte's blog.  He shows you a great example using the Northwind database.

The other method is using a pre-built SP to run the query using temporary pivot-tables.  The solution was posted with a couple samples using the pubs database.  I like this example because you quickly implement a solution...specially if you want to use it in Access! ;-)

Hope this little post helps someone out!