ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
数据管理利器Foxtable2022下载 Excel 2016函数公式学习大典 Office知识技巧免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 Power Query数据清洗实战攻略 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 52660|回复: 162

[分享] 神奇的SQL语言——SQL与行列转制

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2009-5-13 21:43 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:SQL应用
神奇的SQL语言——SQL与行列转制
注:本帖是针对有SQL语言基础的网友所开的贴,里面的很多例子都是广大网友提出来的,很多VBA语句都是摘自其它高手的(本人很喜欢拿来主义,但思路是我自己的),所以如有雷同纯属拿来,希望大家见谅!

论坛里很多人在问如何将他们的表格进行行列转制,如果是简单的将行变成列,那用EXCEL的基本功能就可以了,但很多在转制的过程中有很多的要求,如分类、合计、计数等,通常的VBA编程可以解决,但非常的麻烦!现在我们就来用秘密武器SQL的TRANSFORM语句解决这类问题,下面我们先来了解一下TRANSFORM语句:
语法
TRANSFORM合计函数
    selectstatement
    TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
TRANSFORM 语句可分为以下几个部分:
部分        说明
aggfunction        运算所选数据的 SQL合计函数

selectstatement        SELECT 语句.

pivotfield        在查询结果集中用来创建列标题的字段或表达式。

value1,value2        用来创建列标题的固定值。
说明
使用交叉表查询来摘要数据时,从指定的字段或表达式中选定值作为列标题,这样,可以用比选定查询更紧凑的格式来观察数据。
TRANSFORM是可选的,但当被包含时为 SQL字符串的第一句。它出现在 SELECT 语句(指定作为行标题的字段的)之前,还出现在 GROUP BY 子句(指定行分组的)之前。可以有选择地包含其它子句,例如 WHERE 子句,它指定附加的选择或排序条件。也可以将子查询当作谓词,特别是在叉表查询的 WHERE 子句中。
pivotfield 返回的值被用作查询结果集中的列标题。例如,在交叉表查询中,将根据销售图表按销售月份创建 12 个列。可以限制 pivotfield 用列在可选的 IN 子句中的固定值(value1, value2)来创建标题。也可以用没有数据存在的固定值来创建附加的列。



看完了肯定是一头雾水,本菜也是用了很久才理解,下面就让我来举个例子:
例一:
规格        别名
a        1
b        2
c        3
d        4
e        5
a        6
b        7
c        8
d        9
e        10
要求将规格里的明细横向排列,别名跟在和规格相同的明星下面,如下结果:
规格        a        b        c        d        e
a        1                                    
b                 2                           
c                          3                  
d                                   4         
e                                            5
a        6                                    
b                 7                           
c                          8                  
d                                   9         
e                                            10
我们的编程语句如下:
Sub 行列转制1()
      
    Set rngt = Sheets("新表").Range("A1").CurrentRegion
        Add = rngt.Address(0, 0)
     
   Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
  
    strSQL = "TRANSFORM 别名 SELECT 规格 FROM [新表$" & Add & "] GROUP BY 别名 PIVOT  规格 "

  rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
For Each Field In rs.Fields
aa = Field.Name
[E2].Offset(0, i) = Field.Name

i = i + 1
Next

Sheets("新表").Range("E3").CopyFromRecordset rs
Set rs = Nothing
Set cnn = Nothing

  
End Sub

其它的我就不在这里给大家解释了,如果有机会在开新帖,这里主要是讲下面的关键语句:
strSQL = "TRANSFORM 别名 SELECT 规格 FROM [新表$" & Add & "] GROUP BY 别名 PIVOT  规格 "
TRANSFORM——是固定格式
别名——需要合计的列名,这里就是要找到的别名下的数据
1                                    
         2                           
                  3                  
                           4         
                                    5
6                                    
         7                           
                  8                  
                           9         
                                    10

SELECT 规格 FROM [新表$" & Add & "] GROUP BY 别名——正常的SQL语句,取得我们想要的基本表格
规格                                             
a                                             
b                                             
c                                             
d                                             
e                                             
a                                             
b                                             
c                                             
d                                             
e                                             
PIVOT  规格——返回的值被用作查询结果集中的列标题,实际就是我们要得到的列标题!
a        b        c        d        e
这样我们就组合好了我们想要的结果。
附件:

行列转制1.rar

10.09 KB, 下载次数: 1544

评分

参与人数 6财富 +40 鲜花 +8 技术 +2 收起 理由
扑腾 + 2 太强大了
ardu95 + 2
吴狄 + 2
看见星光 + 10 优秀作品
LMY123 + 2 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-13 21:45 | 显示全部楼层
例二:
表格如下:
日期        材料编号        数量
1月2日        a        10
1月7日        a        11
1月3日        d        5
1月5日        d        70
1月8日        c        50
2月4日        c        43
2月6日        d        33
1月1日        s        20
1月4日        f        69
1月6日        f        32
2月5日        s        33
2月7日        s        27
3月6日        g        27
5月6日        l        10
要求按照材料编号,根据日期中的月份汇总数量,如下表:
材料编号        1月        2月        3月        5月
a        21                           
c        50        43                  
d        75        33                  
f        101                           
g                          27         
l                                   10
s        20        60                  
我们的SQL语句如下:
Sub 行列转制2()
   
   
    Set rngt = Sheets("出货统计").Range("a1").CurrentRegion
        sAddress = rngt.Address(0, 0)
   
   
   Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
  
   
    strSQL = "TRANSFORM SUM(数量) SELECT 材料编号 FROM [出货统计$" & sAddress & "] GROUP BY 材料编号 PIVOT DatePart(""m"", 日期) & '月'"

  rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
For Each Field In rs.Fields
aa = Field.Name
[E1].Offset(0, i) = Field.Name

i = i + 1
Next

Sheet1.Range("E2").CopyFromRecordset rs
Set rs = Nothing
Set cnn = Nothing

  
End Sub

关键语句:
strSQL = "TRANSFORM SUM(数量) SELECT 材料编号 FROM [出货统计$" & sAddress & "] GROUP BY 材料编号 PIVOT DatePart(""m"", 日期) & '月'"

TRANSFORM——是固定格式
SUM(数量)——对数量的合计方式,可以是COUNT的计数方式或其他合计函数,在这里我们要求合计,所得的结果将填入合适的位置。
SELECT 材料编号 FROM [出货统计$" & sAddress & "] GROUP BY 材料编号——得到表格的基本条件
材料编号
a
c
d
f
g
l
s

PIVOT DatePart(""m"", 日期) & '月'"——按照月份形成的列标题,也可以直接这样:
PIVOT日期,直接得到以所有日期列里的日期为列标题的合计数据。

[ 本帖最后由 wsri 于 2009-5-13 22:11 编辑 ]

行列转制2.rar

10.93 KB, 下载次数: 1018

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-13 21:47 | 显示全部楼层
例三:
表格如下:
序号        部门        学历
1        bm1        大学
2             bm2         大专
3              bm1        大学
4                      bm2         大学
5            bm1        中专
                   
要求按在部门统计三种学历的数量,结果如下:
部门        大学        大专        中专
bm1        2                 1
bm2         1        1         

我们的SQL语句如下:
Sub 行列转制3()
   
    Set rngt = Sheets("新表").Range("a1").CurrentRegion
        sAddress = rngt.Address(0, 0)
   
   
   Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
  
   
strSQL = "TRANSFORM count(学历) SELECT 部门 FROM [新表$" & sAddress & "] GROUP BY 部门 PIVOT  学历 in(大学,大专,中专)"
  
  rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
For Each Field In rs.Fields
aa = Field.Name
[E1].Offset(0, i) = Field.Name

i = i + 1
Next

Sheets("新表").Range("E2").CopyFromRecordset rs
Set rs = Nothing
Set cnn = Nothing

  
End Sub
关键语句:
strSQL = "TRANSFORM count(学历) SELECT 部门 FROM [新表$" & sAddress & "] GROUP BY 部门 PIVOT  学历 in(大学,大专,中专)"

TRANSFORM——是固定格式
count(学历)——对学历的计数方式,这里强调必须是SQL合计函数,主要有一下几种:
Avg 函数
Count 函数
First, Last 函数
Min, Max 函数
StDev, StDevP 函数
Sum 函数
Var 和 VarP 函数
他们的用法自己可以在网上找到,我就不啰嗦了!
PIVOT  学历 in(大学,大专,中专)——按照排好的顺序来显示列顺序,如果没有in(大学,大专,中专),则按照汉语拼音的方式排列!
例四:
表格如下:
款号        配色        面料开发厂        编号        物料名称        颜色        使用部位说明
ALT1818        -1        福田        KZ015        40S/2全棉平纹190g        09深蓝        主身
ALT1818        -1        振兴        KZ011        亮光布130g        34青金色        拼条、牙子
ALT1818        -1        福田        KZ015        40S/2全棉平纹190g        01正白        拼条
ALT1818        -1        福田        KZ042        1X1棉罗纹260g        09深蓝        领口
ALT1818        -2        福田        KZ015        40S/2全棉平纹190g        09深蓝        主身
ALT1818        -2        振兴        KZ011        亮光布130g        34青金色        拼条、牙子
ALT1818        -2        福田        KZ015        40S/2全棉平纹190g        01正白        拼条
要求加入配色列,统计颜色,结果如下:
款号        面料开发厂        编号        物料名称        使用部位说明        -1        -2        -3
ALT1818        振兴        KZ011        亮光布130g        拼条、牙子        34青金色        34青金色         
ALT1818        福田        KZ015        40S/2全棉平纹190g        主身        09深蓝        09深蓝         
ALT1818        福田        KZ015        40S/2全棉平纹190g        拼条        01正白        01正白         
ALT1818        福田        KZ042        1X1棉罗纹260g        领口        09深蓝                  
代码如下:
Sub 行列转制4()
   Sheets("总表").Select
    maxrow = Sheets("总表").[b65536].End(xlUp).Row
    Set rngt = Sheets("总表").Range("a2").CurrentRegion
        sAddress = rngt.Address(0, 0)
   
   
   Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
  
   
strSQL = "TRANSFORM First(颜色) SELECT 款号,面料开发厂,编号,物料名称,使用部位说明 FROM [总表$" & sAddress & "] " & _
         "GROUP BY 款号,面料开发厂,编号,物料名称,使用部位说明 PIVOT  配色 in(-1,-2,-3) "
  
  rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
For Each Field In rs.Fields
aa = Field.Name

Sheets("想要的结果").Select

Sheets("想要的结果").[a2].Offset(0, i) = Field.Name

i = i + 1
Next

Sheets("想要的结果").Range("a3").CopyFromRecordset rs
Set rs = Nothing
Set cnn = Nothing

  
End Sub

关键语句:
strSQL = "TRANSFORM First(颜色) SELECT 款号,面料开发厂,编号,物料名称,使用部位说明 FROM [总表$" & sAddress & "] " & _
         "GROUP BY 款号,面料开发厂,编号,物料名称,使用部位说明 PIVOT  配色 in(-1,-2,-3) "

First(颜色)——First函数是在查询所返回的结果集中,第一个记录或最后一个记录所返回的字段值。利用这个我们可以返回颜色里的描述。
SELECT 款号,面料开发厂,编号,物料名称,使用部位说明 FROM [总表$" & sAddress & "] " & _
         "GROUP BY 款号,面料开发厂,编号,物料名称,使用部位说明——返回我们想要的行标
款号        面料开发厂        编号        物料名称        使用部位说明
ALT1818        振兴        KZ011        亮光布130g        拼条、牙子
ALT1818        福田        KZ015        40S/2全棉平纹190g        主身
ALT1818        福田        KZ015        40S/2全棉平纹190g        拼条
ALT1818        福田        KZ042        1X1棉罗纹260g        领口
PIVOT  配色 in(-1,-2,-3)——添加配色列。

[ 本帖最后由 wsri 于 2009-5-13 22:45 编辑 ]

行列转制3.rar

9.63 KB, 下载次数: 953

行列转制4.rar

13.71 KB, 下载次数: 944

TA的精华主题

TA的得分主题

发表于 2009-5-13 22:03 | 显示全部楼层
非常好,值得学习,借鉴一下

补充内容 (2019-8-2 15:59):
10年以后回头年,依然很有价值。心中充满感激啊!

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-5-13 22:49 | 显示全部楼层
哎!也不知道说明白了没有,如果我有山菊花老师的文采,也许效果会更好!
下面是一些资料,也许大家会有用!

[ 本帖最后由 wsri 于 2009-5-13 22:58 编辑 ]

SQL循序渐进.rar

28.41 KB, 下载次数: 1371

ADO.rar

784.98 KB, 下载次数: 1730

MicrosoftJetSQL语言参考.rar

201.87 KB, 下载次数: 1530

TA的精华主题

TA的得分主题

发表于 2009-5-14 09:09 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-14 12:24 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-14 13:38 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-18 21:39 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-5-19 03:29 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2021-12-4 22:01 , Processed in 0.071793 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2021 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表