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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More