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）内选择标题。也可以包含固定值用于没有数据来创建其他列的情况。