Pivot operator in SQL Server 2005 is very useful for displaying a crosstab report. Using the
DataGrid control, we can easily display a crosstab report in a Windows form. This article shows you how to do it without writing any code.
First, we need to create a table to manipulate and show the data. Using SQL2005 Server Management Studio, you should be able to crate a table as in Table 1. The table name I used was Pivt. You are free to use any name.
Next, you can try out the
Pivot SQL as in listing 1 in the SQL2005 query window. This serves as a check that the statement is correct and the
Pivot SQL produces the desired result.
Next, in Visual Studio 2005, open a new Windows project and create a “
DataTableadapter”. Make all the necessary connections to the database server. While in the process of creating the “
DataTableAdapter”, paste the SQL
Pivot statement in the query window. Continue with all the steps until the “
DataTableAdapter” is created. In the “
DataTableAdapter”, add in all the new columns and select the respective data source one at a time, as shown in Figure 1.
Finally, drag and drop the dataset from the data source on to the form, and execute the form once you have finished. A cross tab report similar to Figure 2 will appear.
Points of Interest
A simple Pivot table has been created without writing any C# or VB code. The aggregate function used in the
Pivot statement was “
SUM”. If you need to check the total count you could change it to “
COUNT”. It should be pointed out that in normal circumstances, the query window in Visual Studio will not support a
Pivot statement. However, in this article, we show that it can be done.