神奇的SQL语言——SQL与行列转制

wsri

神奇的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
这样我们就组合好了我们想要的结果。
附件：

wsri

例二：
表格如下：
日期        材料编号        数量
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 编辑 ]

wsri

例三：
表格如下：
序号        部门        学历
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 编辑 ]

一_一

非常好,值得学习,借鉴一下

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

wsri

哎！也不知道说明白了没有，如果我有山菊花老师的文采，也许效果会更好！
下面是一些资料，也许大家会有用！

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

ningyuanchao

感谢楼主，很实用，收藏先。

kzb

下载学习，感觉很实用

jilijun

感谢分享！收藏备用！

bibisin

谢谢LZ分享,学习了

nzhyj

非常好,值得学习鉴一下