- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- C版深夜写实例:纵向表如何变成横向表....大家来学习. (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=1615)
-- 发布时间：2009/1/12 23:04:00
-- C版深夜写实例:纵向表如何变成横向表....大家来学习.
假设有表A,
经过统计后得出统计表A,
表B为固定表,
怎么才能把统计表A的数据写入表B相应的空格,而缺少的项目则自动为0??
表A
| A | 2 |
| A | 3 |
| B | 2 |
| B | 4 |
| D | 3 |
| A | 5 |
| B | 6 |
| D | 3 |
表B
| A | B | C | D | E |
-- 发布时间：2009/1/13 0:33:00
--
如果这种格式是固定的，可以直接在表B的DataColChanged事件中设置如下代码。
e.DataRow("A") = DataTables("表A").Compute("sum(第二列)","[第一列] = \'A\'")+0
e.DataRow("B") = DataTables("表A").Compute("sum(第二列)","[第一列] = \'B\'")+0
e.DataRow("C") = DataTables("表A").Compute("sum(第二列)","[第一列] = \'C\'")+0
e.DataRow("D") = DataTables("表A").Compute("sum(第二列)","[第一列] = \'D\'")+0
e.DataRow("E") = DataTables("表A").Compute("sum(第二列)","[第一列] = \'E\'")+0
然后在表B的MainTableChanged事件中设置。
If Maintable.Name = "表B" Then
DataTables("表B").DataCols("A").RaiseDataColChanged()
End If
-- 发布时间：2009/1/13 0:35:00
--
如果一定要生成统计表，也可以这样，按钮代码：
Dim cmd As New SQLCommand
Dim 统计表A As DataTable
cmd.CommandText = "Select 第一列 , Sum(第二列) AS 第二列 From {表A} Group by 第一列"
统计表A = cmd.ExecuteReader()
Dim dt As Table = Tables("表B")
Dim dt1 As DataTable = DataTables("表A")
Dim ColNames() As String = {"A","B","C","D","E"}
For Each ColName As String In ColNames
Dim Value As Integer
Value = dt1.Compute("sum(第二列)","[第一列] = \'" & ColName & "\'")
dt.Rows(0)(ColName) = Value
Next
-- 发布时间：2009/1/13 0:40:00
--
要代码短也可以这样：
Dim cmd As New SQLCommand
Dim 统计表A As DataTable
cmd.CommandText = "Select 第一列 , Sum(第二列) AS 第二列 From {表A} Group by 第一列"
统计表A = cmd.ExecuteReader()
Dim ColNames() As String = {"A","B","C","D","E"}
For Each ColName As String In ColNames
Tables("表B").Rows(0)(ColName) = DataTables("表A").Compute("sum(第二列)","[第一列] = \'" & ColName & "\'")+0
Next
-- 发布时间：2009/1/13 8:28:00
--
C版必胜,申请加精!!!谢谢C版,举一反三,问题迎刃而解,收藏做为示例了...
-- 发布时间：2009/1/13 9:48:00
--
收藏精典