垂直表和水平表的转换

在一些场合,可能需要对表的结构进行横竖转换。

示例一

假定表A的结构如下:

希望将其转换为表B,结构为:

转换代码为:

Dim dtb As New DataTableBuilder("B")
dtb.AddDef(
"姓名", Gettype(String), 32)
For Each
v As String In DataTables("A").GetValues("课程")
   
dtb.AddDef(v, Gettype(Double))
Next

dtb.Build()

For Each
v As String In DataTables("A").GetValues("姓名")
   
Dim dr1 As DataRow = DataTables("B").AddNew()
    dr1(
"姓名") = v
   
For Each dr2 As DataRow In DataTables("A").Select("姓名 = '" & v & "'")
       dr1(dr2("课程")) = dr2("分数")
   
Next
Next
MainTable = Tables(
"表B")

我们也可以SQL语句来实现,更加简单:

Select 姓名,Sum(iif(课程 = '语文',分数,0)) As 语文,Sum(iif(课程 = '数学',分数,0)) As 数学,Sum(iif(课程 = '英语',分数,0)) As 英语,Sum(iif(课程 = '物理',分数,0)) As 物理,Sum(iif(课程 = '化学',分数,0)) As 化学 From {学生成绩} Group by 姓名

上述SQL语句巧妙地利用了分组统计,希望大家细细体会一下其中的奥妙。
需要注意的是,如果使用的数据源是SQL Server,是不能使用iif函数的,需要用Case When语句:

Select 姓名,Sum(Case When 课程 = '语文' Then 分数 Else 0 End) As 语文,Sum(Case When 课程 = '数学' Then 分数 Else 0 End) As 数学,Sum(Case When 课程 = '英语' Then 分数 Else 0 End) As 英语,Sum(Case When 课程 = '物理' Then 分数 Else 0 End) As 物理,Sum(Case When 课程 = '化学' Then 分数 Else 0 End) As 化学 From {学生成绩} Group by 姓名

我们可以新建一个查询表,或者用QueryBuilder,将其Select语句设置为 上述语句即可。

示例二

接下来更复杂点,假定有下图所示的学生成绩表,在上面的基础上多了一个班级列:

需要将其转换为水平结构的表,而且在原有科目的基础上,再增加一个总分列:

可以参考下面的代码:

Dim dtb As New DataTableBuilder("B")
dtb
.AddDef("班级",Gettype(String),10)
dtb
.AddDef("姓名",Gettype(String),10)
For
Each v As String In DataTables("学生成绩").GetValues("科目")
    dtb.AddDef(v, Gettype(Double))

Next

dtb
.Build()
For
Each v As String() In DataTables("学生成绩").GetValues("班级|姓名")
    Dim dr1 As DataRow = DataTables(
"
B").AddNew()
    dr1(
"
班级") = v(0)
    dr1(
"
姓名") = v(1)
    For Each dr2 As DataRow In DataTables (
"学生
成绩").Select("班级 = '" & v(0) & "' And 姓名 = '" & v(1) & "'")
        dr1(dr2(
"
科目")) = dr2("分数")
   
Next

Next
DataTables
("B").DataCols.Add("总分",Gettype(Double),"[语文] + [英语] + [数学] + [物理] + [化学]")
MainTable
= Tables("表B")

当然,我们同样也可以直接用SQL语句来实现:

Select 班级,姓名,Sum(iif(科目 = '语文',分数,0)) As 语文,Sum(iif(科目 = '数学',分数,0)) As 数学,Sum(iif(科目 = '英语',分数,0)) As 英语,Sum(iif(科目 = '物理',分数,0)) As 物理,Sum(iif(科目 = '化学',分数,0)) As 化学,Sum(分数) As 总分 From {学生成绩} Group by 班级,姓名

上述SQL语句巧妙地利用了分组统计,希望大家细细体会一下其中的奥妙。
需要注意的是,如果使用的数据源是SQL Server,是不能使用iif函数的,需要用Case When语句:

Select 班级,姓名,Sum(Case When 科目 = '语文' Then 分数 Else 0 End) As 语文,Sum(Case When 科目 = '数学' Then 分数 Else 0 End) As 数学,Sum(Case When 科目 = '英语' Then 分数 Else 0 End) As 英语,Sum(Case When 科目 = '物理' Then 分数 Else 0 End) As 物理,Sum(Case When 科目 = '化学' Then 分数 Else 0 End) As 化学,Sum(分数) As 总分 From {学生成绩} Group by 班级,姓名

我们可以新建一个查询表,或者用QueryBuilder,将其Select语句设置为 上述语句即可。

示例三

现在反过来,假定有个水平结构的表:

需要转换为垂直结构的表:

转换代码为:

Dim dtb As New DataTableBuilder("表B")
dtb
.AddDef("班级", Gettype(String), 4)
dtb
.AddDef("姓名", Gettype(String), 10)
dtb
.AddDef("科目", Gettype(String), 10)
dtb
.AddDef("分数", Gettype(Double))
dtb
.Build()
Dim
kms() As String = {"语文","数学","英语","物理","化学"}
For Each dr1 As DataRow In DataTables("成绩表"
).DataRows
    For Each km As String In kms
        Dim dr2 As DataRow = DataTables("表B").AddNew()
        dr2("班级") = dr1("班级")
        dr2("姓名") = dr1("姓名")
        dr2("科目") = km
        dr2("分数") = dr1(km)
    Next

Next
MainTable
= Tables("表B")

我们也可以用SQL语句实现,新建一个查询表,或者用QueryBuilder,将其Select语句设置为:

Select 班级,姓名,'语文' As 科目,语文 As 分数 From {学生成绩} Union All Select 班级,姓名,'数学' As 科目,数学 As 分数 From {学生成绩} Union All Select 班级,姓名,'英语' As 科目,英语 As 分数 From {学生成绩} Union All Select 班级,姓名,'化学' As 科目,化学 As 分数 From {学生成绩} Union All Select 班级,姓名,'物理' As 科目,物理 As 分数 From {学生成绩}

上面的语句用Union All连接了5个查询,每个查询针对一个科目,以第一个科目语文为例,其Select语句为:

Select 班级, 姓名, '语文' As 科目,语文 as 分数 From {学生成绩}

这个Select语句生成的表包括四列,分别是班级、姓名、科目、分数,其中班级、语文就是原始成绩表的班级列和语文列,而科目列的内容固定为"语文",分数列就是原始成绩表的语文列,只不过被重命名为分数列。

其它科目的查询语句一样,用Union All组合每个科目的查询语句,就得到我们所需要的表了。


本页地址:http://www.foxtable.com/webhelp/scr/2965.htm