VBA利用transform函数和ADO实现交叉汇总以及数据集转置
一直想在Excel中通过VBA调用ADO查询统计excel数据的时候直接实现数据集行列转置,但是在网上都没有找到解决方案,能找到的基本都是得到RecordSet后转换成数组再转置,今天在研究下面需求的时候突然发现了解决方案。
业务场景一:
假设存在三列数据:受理人、受理日期、业务量,怎么变成表:受理人、(按照受理日期的数据作为列名、业务量作为数据)?
使用类似下面的sql语句,通过vba执行Excel:
transform sum(业务量) SELECT 受理人 from 原来的三列数据表 group by 受理人 pivot 受理日期
业务场景二:
上面的数据,如果数据集中存在 某个受理人在某个受理日期有多行记录,那么sum(业务量)会相加求和,但是,如果只有一行数据呢?
那么就间接实现了行列转置。
以为很容易,实际上没有那么容易。transform的使用有一定条件,包括需要和聚合(group by)一起使用。
如果只是简单的讲行列转置,并不需要count或者sum等聚合操作,该怎么办呢?
在解决过程中,我也发现在vba中调用ado间接实现excel环境的虚拟表,类似oracle中的 select 2*3 from dual,不会产生多余的列和数据。
附录:
Transform基本语法:
Creates a crosstab query.
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
部分 说明
aggfunction 对所选数据进行计算的 SQL 聚合函数。
selectstatement SELECT 语句。
pivotfield 希望用于创建查询结果集中列标题的字段或表达式。
value1、value2 用于创建列标题的固定值。
说明:
使用交叉表查询汇总数据时,将从作为列标题的指定字段或表达式中选择值,以便能够以一种比使用选择查询更紧凑的方式来查看数据。
TRANSFORM 是可选的,但如果包括它,则应为 SQL 字符串中的第一个语句。它在指定作为行标题的字段的 SELECT 语句之前,在指定行分组方法的 GROUP BY 子句之前。您也可以包含其他子句(例如。指定其他选择或排序条件的 WHERE)。还可以在交叉表查询中使用子查询作为谓词,特别是在 WHERE 子句中。
pivotfield 中返回的值作为查询结果集中的列标题。例如,在交叉表查询中如果依据月销售量来透视销售数据,将会创建 12 个列。可以约束 pivotfield 以便从可选 IN 子句中所列出的固定值(value1,value2)内选择标题。也可以包含固定值用于没有数据来创建其他列的情况。