TRANSFORM 语句的详解

Scarlett_88

。       TRANSFORM aggfunction
                selectstatement
                PIVOT pivotfield [IN (value1[, value2[, ...]])]

。       TRANSFORM 语句包含以下部分：
•        部分说明aggfunction操作所选数据的 SQL 聚合函数。selectstatementSELECT 语句。pivotfield希望用于创建查询结果集中列标题的字段或表达式。value1，value2用于创建列标题的固定值。
•        说明
•        使用交叉表查询汇总数据时，将从作为列标题的指定字段或表达式中选择值，以便能够以一种比使用选择查询更紧凑的方式来查看数据。
•        TRANSFORM 是可选的，但被包括时则是 SQL 字符串中的第一个语句。它在 SELECT 语句之前以指定作为行标题的字段，在 GROUP BY 子句之前以指定行分组方法。您也可以包含其他子句（如 WHERE），以指定其他选择或排序条件。还可以在交叉表查询中使用子查询作为谓词，特别是在 WHERE 子句中。
•        pivotfield 中返回的值作为查询结果集中的列标题。例如，在交叉表查询中如果依据月销售量来透视销售数据，将会创建 12 个列。可以约束 pivotfield 以便从可选 IN 子句中所列出的固定值（value1，value2）内选择标题。也可以包含固定值用于没有数据来创建其他列的情况

该语句可以用于OLE EB 窗体，也可以用于VBA中。
我们来用范例讲解一下这个SQL语句
http://club.excelhome.net/viewth ... highlight=Transform
我的SQL语句如下：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
group by CCY
PIVOT col

今天我出差回来了，继续这个话题。
TRANSFORM 改变的意思
PIVOT 计算机中就是透视表的意思
aggfunction SQL的 聚合函数，既然是聚合函数，那么就要用到group by
数据透视表说白了就是实现聚合功能，但没SQL的强，一般就是实现求和和求个数的功能，
如果你将所有行字段和列字段都用用SQL的group by来求和或求个数，然后用数据透视表的求和显示，数据源和结果都是唯一的。如果是原始数据源，用数据透视表实现的就是group by 的功能，
TRANSFORM 。。。PIVOT SQL语句有一些限制：

1、只有一个聚合函数，
2、只有一个字段的不重复记录变成列字段。
很容易明白，将数据处理后的结果为一个2维的新表，保留数据透视表的数据结构，但显示的结果更像一个新的结果表
3、Select 后跟的行字段可以多个，所有有人用连接符来求行字段就不可取了，但别忘了所取的行字必须出现在Group by 后面的行字段。
4、[IN (value1[, value2[, ...]])]，有时取为列的字段有很多项目类别，甚至有可能超出256，这样结果可能报错，同时我们往往关系的是其中几个项目类，其他的不关心，这样我们就可以选择我们要的项目作为新表的列，我们只要将记录中要的字段写入括号就可以了。
如果我们只有后面有M字符的记录
语句可以写成为：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
group by CCY
PIVOT col in ('1M','2M','3M','4M','5M','6M','9M')

等效SQL语句有：
transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col in ('1M','2M','3M','4M','5M','6M','9M')
group by CCY
PIVOT col


transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col like '%M'
group by CCY
PIVOT col

transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join  
(select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col ='1M' or col ='2M' or col ='3M' or col ='4M' or col ='5M' or col ='6M' or col ='9M'
group by CCY
PIVOT col
共4种方式写的SQL语句

第一次采用1-4的SQL语句测试速度，第二次采用4-1的SQL语句测试速度，速度差不多，两次统计如下：
1                  2             3              4
2.483         2.486         2.505         2.523
4                  3              2             1
2.838         2.844         2.853         2.856
数据量少可能不能说明什么，真正用于数据处理的时间同打开文件等等相比可能较小，这样速度就不是特别重要的因素。个人认为第3个语句比较简洁。
附件提供有编程代码，有兴趣的朋友可以测试一下，我将数据输出屏蔽了，要实现在result工作表中显示结果，将后面屏蔽部分恢复过来就是。
针对该SQL语句，给大家相信分析一下。

transform sum(data) select CCY from
(
select x.CCY,x.col,y.data
from
(
select CCY,col
from
(select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(select LEFT(RIC,3) AS CCY
from [Data$]) b
group by CCY,col) x
left join  
(
select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
) y
on x.CCY=y.CCY
union all
select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
)
where col like '%M'
group by CCY
PIVOT col

select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
提取后面要用的记录中的将要用作列字段的信息

select LEFT(RIC,3) AS CCY
from [Data$]
提取CCY所有字段

select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(
select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col
将CCY和col组合起来的记录，记录数会是两个数相乘。肯定覆盖所有CCY和col组合在一起的记录。
不用这种方法。既含有CCY又含有col字段的记录就不一定都存在。

select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
将每个都要相加的平均值并命名为SPOT的记录提取去来。

select x.CCY,x.col,y.data
from
(
select CCY,col
from
(
select MID(RIC,4,LEN(RIC)-4) as col
from [Data$]
where len(RIC)>4
)a,
(
select LEFT(RIC,3) AS CCY
from [Data$]
) b
group by CCY,col
) x
left join  
(
select LEFT(RIC,3) AS CCY, 'SPOT' as col,(BID+ASK)/2 as data
from [Data$]
where len(RIC)=4
) y
on x.CCY=y.CCY
前面CCY和col组合的所有记录添加字段用left join 方式被join的数据填充。
这样就给每个CCY和col字段添加了一个都要相加的数据。

select LEFT(RIC,3) AS CCY, MID(RIC,4,LEN(RIC)-4) as col,(BID+ASK)/20000 as data2
from [Data$]
where len(RIC)>4
将所有拆分字段形成的记录做数据处理，求平均再除以10000.

将两个数据源Union all这样我们可以用Union 好的数据做统计。

最后的结果就是筛选条件，按数据透视表的样式来形成新的表。

附件于7月26日已作更新。

[ 本帖最后由 Scarlett_88 于 2009-7-26 15:28 编辑 ]

BIN_YANG168

感谢Scarlett_88版主，学习了。

dsp5000

研究一下。

xowi99

学习下

nkgsm

象看天书一样，难度大了点，看来这阳春白雪只有版主专家们去研究了，我们还是希望来点下里巴人。

小蚁雄兵

原帖由 nkgsm 于 2009-7-23 15:31 发表
象看天书一样，难度大了点，看来这阳春白雪只有版主专家们去研究了，我们还是希望来点下里巴人。

这个语句在列数不确认的时候还是蛮有用的，它本身的语法并不复杂的。

yanjie

谢谢分享！加点解释就好了！

[ 本帖最后由 yanjie 于 2009-7-24 20:47 编辑 ]

fenghcl

语法语句看不明白.能逐句解释最好.

小蚁雄兵

以下语句提示错误“在子查询中，不可多层GROUP BY 子句”

TRANSFORM SUM(F4) SELECT F1 FROM ( SELECT F1,F2,f3,(SELECT sum(F3) FROM [SHEET1$]) AS F4 FROM [Sheet1$] ) GROUP BY F1 PIVOT F2

貌似TRANSFORM　后面接的聚合函数不能引用标量查询生成的字段？

[ 本帖最后由 小蚁雄兵 于 2009-7-25 13:39 编辑 ]

小蚁雄兵

我猜测TRANSFORM 不会参与SQL语句的任何计算，仅仅是把GROUP BY 的结果集进行行列转置。