VBA SQL 一维表转二维表

VBA SQL 一维表转二维表

昨天刷了一题使用SQL二维表转一维表

那么今天就练习下一维表转二维表

如下图所示

TransForm语句用于创建交叉表查询,该查询用于对记录的聚合,然后按照数据的类别进行分组,一组在于左端,而另一组在于顶端...

语法为

TransForm 聚合值字段 Select 行字段 From 表 Group By 分组行字段 Pivot 列字段 [In Value...]

TransForm有两个比较值得注意的是:

TransForm支持Where子句,而不支持Having子句TransForm只能作为查询表结果的存在,而不能嵌套在其他语句的内部使用

例如需要做成以下交叉表,也就是常见的数据透视表

使用SQL,语法如下

strSQL = "TransForm Sum(分数) Select 年级,姓名,Sum(分数) As 总分 From " & strSource & " Group By 年级,姓名 Pivot 学科"

Select语句部分,指定了交叉表的分组行字段,既年级以及姓名Pivot指定了交叉表的列字段,也就是学科TransForm Sum(分数) 通过聚合函数Sum求和,返回对行字段以及列字段共同分组定义的值,当然这里使用平均(Avg),计数(Count),最大值(Max)等都是可以的,就和透视表一样

如果需要做到和透视表一样在下方有总计的字样,就需要在Union合并数据源

语法如下

strSQL1 = "Select * From " & strSource & " Union All Select '总计' As 年级,Null As 姓名,学科,Sum(分数) From " & strSource & " Group By 学科 " strSQL = "TransForm Sum(分数) Select 年级,姓名,Sum(分数) As 总分 From (" & strSQL1 & ") Group By 年级,姓名 Pivot 学科"

strSQL1使用Union All 合并了数据源以及各个科目分组后的总分,返回以下结果

strSQL1 = "Select * From " & strSource & " Union All Select '总计' As 年级,Null As 姓名,学科,Sum(分数) From " & strSource & " Group By 学科 "

剩下的只是TransForm的常规操作了

如果需要添加每个年级的汇总,例如下图所示

依然还是使用Union合并数据源

语法如下

strSQL1 = "Select * From " & strSource & " Union All Select '总计' As 年级,Null As 姓名,学科,Sum(分数) From " & strSource & " Group By 学科 " _ & " Union All Select 年级&' 汇总' as 年级 ,null,学科,Sum(分数) From " & strSource & " Group By 年级,学科" strSQL = "TransForm Sum(分数) Select 年级,姓名,Sum(分数) As 总分 From (" & strSQL1 & ") Group By 年级,姓名 Pivot 学科"

仅仅也是多了一段语句

Union All Select 年级&' 汇总' as 年级 ,null,学科,Sum(分数) From " & strSource & " Group By 年级,学科

示例文件下载

链接：https://pan.baidu.com/s/1JloGJ4nVdSYS_rde-_UNfw

提取码：abcd

收工!