我們介紹了ms sql server中的roll up語句。下面開始介紹如何用datagrid結合rollup語句來進行分類統計。
我們要達到的效果是這樣的：
首先，應先將數據庫中的產品數據按照所屬的不同的目錄列舉出來，這其中要用到一些技巧.這裡先用SQL語句，從數據庫讀取product表的數據，之後放到dataset的默認datatable中去，然後檢查每一個產品所屬的類別，如果發現某一個產品的類別和前一條記錄中產品所屬的類別不一樣的話，那麼就可以肯定當前產品是屬於一個新的分類了，就可以插入新的行，並且加以修飾，成為分類標題，同時將roll up的統計結果顯示在相應的位置就可以了。我們先來看page_load部分的代碼
Sub Page_Load(Sender As Object, E As EventArgs) Handles MyBase.Load
' TODO: Update the ConnectionString and CommandText values for your application
dim ConnectionString as string = "server=localhost;database=northwind;UID=sa"
Dim CommandText As String = "Select CASE WHEN (Grouping(CategoryName)=1) THEN " & _
"'MainTotal' ELSE categoryname END AS CategoryName, "
CommandText &= " CASE WHEN (Grouping(ProductName)=1) THEN 'SubTotal' ELSE " & _
"Productname END AS ProductName,"
CommandText &= " Sum(UnitPrice) as unitprice, "
CommandText &= " Sum(UnitsinStock) as UnitsinStock "
CommandText &= " from Products INNER JOIN Categories On Products.categoryID = " & _
" Categories.CategoryID"
CommandText &= " Group By Categoryname, ProductName WITh ROLLUP "
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet
myCommand.Fill(ds)
Dim curCat As String ‘指示當前記錄中產品所屬的類別
Dim prevCat As String ‘指示上一條記錄中產品所屬的類別
Dim i As Integer = 0 ‘要插入分類標題行的位置，用I表示
'遍歷結果集，找出要插入分類標題的行
Do While i <= ds.Tables(0).Rows.Count - 1
curCat = ds.Tables(0).Rows(i).Item(0)
If curCat <> prevCat Then ‘如果發現前後兩記錄的所屬類別不一樣
prevCat = curCat
Dim shRow As DataRow = ds.Tables(0).NewRow
shRow(1) = ds.Tables(0).Rows(i).Item(0)
'Change ItemDataBound marker to Negative Number
shRow(2) = -1 ‘‘設置一個臨時的標記
ds.Tables(0).Rows.InsertAt(shRow, i)
i += 1
End If
i += 1
Loop
‘將最後一行的標題改為total
ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1).Item(1) = "Total"
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub 
