FillXTabTable(). All that's left is to fill the XTab DataTable with data by using the FillXTabTable() procedure that the code at callout D shows. The procedure begins by dimensioning the object variables, then loops through the rows in the Authors table. For each row in the Authors table, the code creates a new DataRow for the XTab DataTable and puts the author's name in the first column—Column(0). The rest of the columns in the new row contain zeros by default.
Next, the code invokes the Author DataRow's GetChildRows method on the DataRelation object to retrieve from the Sales table an array of DataRows that are linked to the author. The array (arowSales) contains one DataRow for each store that had sales for that author. For example, GetChildRows returns a one-element array for author Sheryl Hunter, who had sales only at Barnum's bookstore.
The code then loops through the author's sales DataRows and places each sales total under the XTab column identified in the stor_name field of that sales DataRow. Stores for which the author has no sales retain the default value, 0. When the XTab row is finished, the code adds it to the DataTable's Rows collection and returns to the top of the loop to process the next author. When all the authors are processed, the XTab DataTable is finished, as Figure 3 shows.
To view the results in a .NET Windows Forms DataGrid, you can assign the XTab DataTable to the DataGrid's DataSource. If you want the DataGrid to be sorted when it first appears, you can use the code that Listing 7 shows to wrap the table in a DataView, sort the DataView, and assign the DataView to the DataGrid's DataSource.
Two Roads Diverge
These two approaches to creating dynamic crosstab reports produce similar—but not identical—results. The ADO.NET version includes a row for every author in the Authors table, whereas the dynamic SQL version excludes authors who have zero sales across all stores. This difference could be important depending on the purpose of the report. The ADO.NET version gives you the flexibility, through code, to exclude authors with no child records in the Sales table. One way is to simply check the count of rows that the Author DataRow's GetChildRows method returns. If no sales records exist, the code doesn't add the new row to the crosstab DataTable. The converse, getting the SQL crosstab query to return rows of authors with no sales, is more difficult, but possible. To include all authors in the dynamic SQL version, you might decide to start by adding into the source data at least one dummy sales record that has a sales value of 0 for each author record that otherwise has no sales. Unfortunately, this approach won't work if you're using COUNT() as the summarizing function because those dummy sales will count, too, and you don't want that. The way around this problem is to pre-aggregate the source data by using the summarizing function you want, then add the dummy records, as long as every author gets at least one. Then, in the crosstab query, always use the SUM() function. The code that Listing 8 shows creates a function, dbo.fn_Sales(), that UNIONs the pre-aggregated records from vwSales with a set of dummy records—one for each author-store pair—which you generate by using a cross join. If you replace dbo.vwSales2 in Listing 4's crosstab query with dbo.fn_Sales(), the final result is the same as the one you achieved with ADO.NET: All authors will appear in the report.
An advantage of the dynamic SQL approach is that it doesn't require the Microsoft .NET Framework. If you're using Visual Studio 6.0, for example, you can easily create an ADO recordset from the dynamic SQL crosstab table and bind it to a flex-grid control. On the other hand, the ADO.NET approach isn't necessarily tied to SQL Server. You can easily adapt it for use with databases that don't support CASE expressions, such as Microsoft Access.
Cursors in SQL Server are slow and expensive in terms of resources; they're a last resort when no other alternative is practical. Dynamic SQL is inefficient because SQL Server doesn't reuse the query plans it generates from directly executed SQL strings. You also need to consider security issues when executing SQL strings. I avoid cursors and dynamic SQL wherever possible, and the ADO.NET solution lets me do just that. When I use ADO.NET, I don't worry about the 8000-character limit of varchar variables, and I have better control over my output than with T-SQL, as I mentioned earlier. So as long as I'm working in the .NET Framework, I'll use the ADO.NET solution.