Jayme Davis' WebLog

Yeah, I've ran into wanting to do crosstabs directly in T-SQL/MS SQL Server, and always had to a unique combination of Case, and Count() or First() functions to get what I want.

The downside is that in the Case statement, you have to *know* ahead of time the unique column values you want to display, and sometimes you want all unique values on the fly (dynamically).

You could of write a stored procedure to do a GROUP BY query initially to grab the unique values first, and then loop through the results to dynamically create the Case part of the SELECT statement to simulate the crosstab query. I've actually wanted to write the sp one day, but never got around to it. I've also though about creating a tool to do it as well (althought mine would be free - heh-heh). I might do that later today although it would take more than a day to make it distributable...but it'd be an interesting project.

You are right though, I do hope that the Yukon people write that into the new version since Xtabs are very powerful and should be used more to take a quick and dirty view of the data aggregated similar to something you'd get out of a data warehouse or OLAP.

Kael

^_^,Pretty Good!