SQL中的透视转换

来源：互联网发布：mac beta版什么意思编辑：程序博客网时间：2024/06/03 19:23

一、概念：

是一种把数据从行的状态旋转为列的状态的处理，在这个过程中可能需要对值进行聚合（有需求的话）。

二、实现方式：

1、使用标准SQL

2、使用T-SQL PIVOT运算符

三、涉及三个逻辑处理阶段：

首先是：分组阶段-----分组元素（要处理的行元素）

其次是：扩展阶段-----扩展元素（要处理的列元素）

最后是：聚合阶段-----聚合元素（聚合函数和要聚合的元素）

四、案例演示（基于SQL Server2008）：

1、准备数据

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Orders')DROP TABLE OrdersCREATE TABLE Orders(orderid INT PRIMARY KEY NOT NULL,orderdate DATE NOT NULL,empid INT NOT NULL,custid VARCHAR(5) NOT NULL,qty INT NOT NULL,);INSERT INTO Orders(orderid, orderdate, empid, custid, qty)VALUES(30001,'20070802',3,'A',10),(10001,'20071224',2,'A',12),(10005,'20071224',1,'B',20),(40001,'20080109',2,'A',40),(10006,'20080118',1,'C',14),(20001,'20080212',2,'B',12),(40005,'20090212',3,'A',10),(20002,'20090216',1,'C',20),(30003,'20090418',2,'B',15),(30004,'20070418',3,'C',22),(30007,'20090907',3,'D',30);

2、.需求描述：生成一个报表，包含每个雇员和客户组合之间的总订货量。视图效果如下

这个简单，直接通过如下代码即可实现：

                                   SELECT empid,custid,SUM(qty) AS sumqty FROM Orders                                    GROUP BY empid,custid

可如果要把上面的输出结果转换成以下视图该怎么办呢? 即要求用以下格式显示每个雇员(按行)和客户(按列)的总订货量

这里我们就要用到透视转换了

3、使用标准SQL实现

分组元素：empid

扩展元素：custid

聚合元素：qty

聚合函数：sum()

SELECT empid,SUM(CASE WHEN custid = 'A' THEN qty END) AS A,SUM(CASE WHEN custid = 'B' THEN qty END) AS B,SUM(CASE WHEN custid = 'C' THEN qty END) AS C,SUM(CASE WHEN custid = 'C' THEN qty END) AS DFROM OrdersGROUP BY empid

4、使用PIVOT运算符

建议使用表表达式作为PIVOT运算符的输入表，因为PRVOT运算符的源表只允许包含分组、扩展、聚合元素，

就算源表中只有这个三个元素，而我们也无法预测将来是否会向表中添加新列。而且，也不需显示指定分组元素，

因为PIVOT运算符会隐式地把源表中既不指定为扩展元素又不指定为聚合元素的那些元素作为分组元素。代码如下：

SELECT empid,A,B,C,DFROM   (SELECT empid,custid,qty FROM Orders) AS DPIVOT (SUM(qty) FOR custid IN(A,B,C,D)) AS P;

以上查询语句中的PIVOT运算符并没有直接操作Orders表，而是对一个名为D的派生表进行操作，该派生表只包含

三个透视转换元素。

0 0