I retired from personal blogging in July 2008.
But you can find me over at http://blog.xero.com.
One of the things I miss from the good ‘ol Access database days is CrossTab queries.
Here’s a great article on why CrossTabs should be done in the .Net data layer not at the database.
.NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable
Consider the result of a crosstab operation — the columns returned will vary depending on the data. There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes.
Though when SQL 2005 arrives there are a few more options …
- There is a CrossTab function
- You could put this logic in the database
The article has a great sample Pivot Function in C#. I translated to VB here.
blog