Crosstab result sets in SQL Server

One of the most common questions I get these days is "How do I create a result set in SQL Server that can be manipulated like an Excel crosstab or pivot table?"

The good news for many of us who are ready to upgrade is that Microsoft has built in this capability into SQL Server 2005 via the new PIVOT and UNPIVOT commands.  No doubt, they did this because they got that very same question 100x times more often than I did, and probably from customers in a much worse mood too.

The bad news for those of us still stuck in a SQL Server 2000 application is that crosstabs/pivot tables are harder than ice on New Years Day (For those of you in the tropics and south of the equator, that's as hard as a rock!  But I digress.)  You usually have to figure out some fancy code, put it in a stored procedure, and then go from there.

Since I'm lazy, I like to use the work of other people who are, I hope, even smarter than me.  One such person, Brian Walker, has gone to the trouble of creating a crosstab functionality for you in SQL Server 2000.  You can find it at this link (,289483,sid87_gci1131829,00.html?track=NL-464&ad=525294USCA). 

Enjoy!  And let me know if you've seen other crosstab stored procedures that are as good or better.



Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.