图解SQL Transform与Pivot

fankairong

【注：数据源来源于ExcelTip】完整附件在第8楼
假定有如下数据源
需要生成如下表的形式，应该如何生成呢？

当然你会说用透视表

1. select * from [源数据$A1:C13]
   
2. union all
   
3. select * from [源数据$F1:H17]

复制代码

不过这不在此贴的范围内，此贴只讲述如何用Pivot和Transform来实现。
更可以说是本人学习Transform时的学习总结吧

fankairong

透过需要实现的效果来反推，首先第一步肯定是需要将两个表合并起来。这就用到了Union all也就是一楼的SQL，最终形成如下图的效果

fankairong

第二步可以看结果图里的数据（透视表的数据）期实就是汇总数，只是根据姓名及月份对金额进行分类汇总。这时就需要用到SQL的Group by 语名
通过

1. select 姓名,月份,sum([金额]) as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "]) group by 姓名,月份

复制代码

生成如下格式的表

至此，框架已经出来了，剩下的就是对格式进行重排。即将月份移式列标题（相对于透视表），将姓名去重。

fankairong

上面提过剩下的一步就是转换格式了，那么如何转换格式呢？此时就需要用到Transform...Selct ...()...Group by...Pivot..语法了。这个语法要如何理解呢？正常理解我也不懂，我只能是按数据透视表的方式来理解
Transfrom 数据区域 select 行字段 from (数据源) group by 分组依据 pivot 列字段
如使用如下语句

1. Transform SUM(金额) select 月份 as 合计 from (select * from [源数据[        DISCUZ_CODE_0        ]quot; & sAddress1 & "] union all select * from [源数据[        DISCUZ_CODE_0        ]quot; & sAddress2 & "])   GROUP BY 姓名,月份 pivot 姓名"

复制代码

就会生成如下格式的表格

fankairong

而要是行列互换呢？相信你己经懂了
对，就是用下列SQL

1. Transform SUM(金额) select 姓名 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 月份,姓名 pivot 月份"

复制代码

最终生成如下表格式

fankairong

至此，你已经掌握了80%的Transform...Pivot了为什么说是80%呢？也许你已经发现了，就是我们之前生成的表的行标题并不是唯一的？就是说是有重复的。
这是为什么呢？因为Group语句，
在Transform里，我们只需要按行标题分组即可。
即

1. Transform SUM(金额) select 月份 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 月份 pivot 姓名" '行标题为｛｝，列标题为｛｝分组，去除行标题重复

复制代码

从而生成下图

至此，你可以动手试试将姓名和月份互换，看是否能够生成正确的表？这一步就留给自己动手体验下吧

fankairong

至此，如果有动手试做的家人应该已经发现，可以生成数据但是列标题没有显示出来。这就需要用 field.name了。

1. For Each Field In rs.Fields
   
2.         aa = Field.Name
   
3.         [a19].Offset(0, i) = Field.Name
   
4.         i = i + 1
   
5.     Next

复制代码

当然，这个涉及到ADO的问题了。
感兴趣的同学可以参考下我的另一个贴子
http://club.excelhome.net/thread-896375-1-1.html

fankairong

到这里就已经完全结束了。现附上完整的附件，里面的关键就是strSQL，各位学习的时候建议挨个测试SQL看其效果。测试办法是将后面的strSQL前面做个标注，使其不运行

1. Sub Transform()
   
2.     sAddress1 = Sheets("源数据").Range("a1").CurrentRegion.Address(0, 0)    '将地址用相对引用方法表示，如果用绝对引用的话，会出现多个$从而导至错误
   
3.     sAddress2 = Sheets("源数据").Range("f1").CurrentRegion.Address(0, 0)
   
4. 
   
5.     Dim cnn As New ADODB.Connection
   
6.     Dim rs As New ADODB.Recordset
   
7.     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
8.     strsql = "select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "]"    '第一步合并源数据
   
9.     strsql = "select 姓名,月份,sum([金额]) as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "]) group by 姓名,月份"    '第二步分组
   
10.     strsql = "Transform SUM(金额) select 月份 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 姓名,月份 pivot 姓名"    '行标题为｛｝，列标题为｛｝分组
    
11.     strsql = "Transform SUM(金额) select 姓名 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 月份,姓名 pivot 月份"    '行标题为｛姓名｝，列标题为｛月｝分组
    
12.     strsql = "Transform SUM(金额) select 月份 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 月份 pivot 姓名"    '行标题为｛月｝，列标题为｛姓名｝分组，去除行标题重复
    
13.     strsql = "Transform SUM(金额) select 姓名 as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 姓名 pivot 月份"    '行标题为｛姓名｝，列标题为｛月｝分组，去除行标题重复
    
14.     rs.Open (strsql), cnn
    
15.     [a20].CopyFromRecordset cnn.Execute(strsql)
    
16. 
    
17.     For Each Field In rs.Fields
    
18.         aa = Field.Name
    
19.         [a19].Offset(0, i) = Field.Name
    
20.         i = i + 1
    
21.     Next
    
22. 
    
23.     Set rs = Nothing
    
24.     Set cnn = Nothing
    
25. End Sub

复制代码

数据透视Transform.rar (11.02 KB, 下载次数: 644)

2012-7-30 11:33 上传

点击文件名下载附件

fankairong

另出一个小题，试试如何将透视表的合计移至前面，即如下图所示效果

fankairong

9楼答案

1.     strsql = "Transform SUM(金额) select 姓名,SUM(金额) as 合计 from (select * from [源数据$" & sAddress1 & "] union all select * from [源数据$" & sAddress2 & "])   GROUP BY 姓名 pivot 月份"    '合计前置
   

复制代码

通过灵活的SQL就可以实现很多透视表实现不了的功能