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 sqlteam.com 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!