sql server 2008语言基础: 透视转换

USE tempdb;

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL, -- prior to SQL Server 2008 use DATETIME
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.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);

select empid,custid,SUM(qty) SumQty from Orders
group by empid, custid

得到每个员工在每个客户身上一共有多少个产品数量. 看到这个图. 如果我们想得到下面这个图呢

这就是行转列的问题了.

传统方法.

with t as(
select empid,custid,SUM(qty) SumQty from Orders
group by empid, custid
)
----传统方法.
select empid,
max(case when custid='A' then qty else 0 end) A,
max(case when custid='B' then qty else 0 end) B,
max(case when custid='C' then qty else 0 end) C,
max(case when custid='D' then qty else 0 end) D
 from Orders group by empid

pivot透视法.

--pivot运算符. 也是在查询的from子句的上下文中执行操作. 它对某个源表或者表表达式进行操作, 透视数据.
--再返回一个结果表.  三个步骤, 分组, 扩展, 聚合.
--在pivot运算符的圆括号内要指定聚合函数(本例为sum), 聚合元素(qty), 扩展元素(custid),
--以及目标列名称的列表. 在pivot运算符的圆括号后面, 可以为结果表指定一个别名.

select empid, A,B,C,D,e from 
( 
select empid,custid, qty from Orders
) as t 
pivot(sum(qty) for custid in(A,B,C,D,e))
as p;

这时候, 我们可能会问.

select empid, A,B,C,D,e from 
Orders
pivot(sum(qty) for custid in(A,B,C,D,e))
as p;

这样写行不行呢?

我们来运行一下. 得到. 并没有按empid分组.

 

可以这么理解. 如果直接用order表作为源表. 那么直接就用order表的每一行进行分组. 得到的自然是最小粒度的行.

而

( select empid,custid, qty from Orders ) as t  作为源表. 那么就是用empid, custid, qty三列有资格作为分组列.

可是在下文的代码里. custid成为了扩展列. qty成为了聚合列. 那么还有一个分组列呢. 自然就是empid了.

于是. 就达到了目标.

 

练习

/*

 普通行列转换

 (爱新觉罗.毓华 2007-11-18于海南三亚)

假设有张学生成绩表(tb)如下:

 Name Subject Result

 张三 语文　　74

 张三 数学　　83

 张三 物理　　93

 李四 语文　　74

 李四 数学　　84

 李四 物理　　94

 */

-------------------------------------------------------------------------

 /*

 想变成 

 姓名         语文        数学        物理          

 ---------- ----------- ----------- ----------- 

 李四         74          84          94

 张三         74          83          93

 */

create table tb

 (

    Name    varchar(10) ,

    Subject varchar(10) ,

    Result  int

 )

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)

 insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)

 insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)

 insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)

 insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)

 insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)

 go

--静态SQL,指subject只有语文、数学、物理这三门课程。

 select name 姓名,

   max(case subject when '语文' then result else 0 end) 语文,

   max(case subject when '数学' then result else 0 end) 数学,

   max(case subject when '物理' then result else 0 end) 物理

 from tb

 group by name

 /*

 姓名         语文        数学        物理          

 ---------- ----------- ----------- ----------- 

 李四         74          84          94

 张三         74          83          93

 */

首先写出pivot源表的表表达式.select name,result, [subject] from tb. 其中 name是分组列, result是聚合列. subject是扩展列.

pivot(语法) as 临时表明.

完成最后的select:  select name , 语文,数学,物理..  得到

select name , 语文,数学,物理
from(
select name,result, [subject] from tb
) z pivot(max(result) for [subject] in (语文,数学,物理)) as p