SQL Server 2008中的Pivot和UnPivot

SQL Server 2008中SQL应用系列--目录索引

今天给新成员讲解PIVOT 和 UNPIVOT示例,顺便整理了一下其用法。这是自SQL Server 2005起提供的新功能。

官方示例:http://msdn.microsoft.com/zh-cn/library/ms177410%28v=sql.105%29.aspx

首先看PIVOT示例:

基本表数据:

IF NOT OBJECT_ID('tb_Income') IS NULL
    DROP TABLE [tb_Income]

/****** Object:  Table [dbo].[tb_Income]   Script Date: 2012/4/5 8:19:21 ******/


CREATE TABLE [dbo].[tb_Income](
----[PKID] int primary key identity(101,1),
 [PName] [Nvarchar](20) NOT NULL,
    [CYear] Smallint NOT NULL,
    [CMonth] TinyInt NOT NULL,
    [CMoney] Decimal (10,2) Not Null

    )

GO
INSERT [dbo].[tb_Income] 
SELECT '胡一刀',2011,2,5600
union ALL SELECT '胡一刀',2011,1,5678
union ALL SELECT '胡一刀',2011,3,6798
union ALL SELECT '胡一刀',2011,4,7800
union ALL SELECT '胡一刀',2011,5,8899
union ALL SELECT '胡一刀',2011,8,8877
union ALL SELECT '胡一刀',2011,6,7788
union ALL SELECT '胡一刀',2011,7,6798
union ALL SELECT '胡一刀',2011,10,10000
union ALL SELECT '胡一刀',2011,9,12021
union ALL SELECT '胡一刀',2011,11,8799
union ALL SELECT '胡一刀',2011,12,10002

union ALL SELECT '苗人凤',2011,1,3455
union ALL SELECT '苗人凤',2011,2,4567
union ALL SELECT '苗人凤',2011,3,5676
union ALL SELECT '苗人凤',2011,4,5600
union ALL SELECT '苗人凤',2011,5,6788
union ALL SELECT '苗人凤',2011,6,5679
union ALL SELECT '苗人凤',2011,7,6785
union ALL SELECT '苗人凤',2011,8,7896
union ALL SELECT '苗人凤',2011,9,7890
union ALL SELECT '苗人凤',2011,10,7799
union ALL SELECT '苗人凤',2011,11,9988

union ALL SELECT '郑希来',2011,2,5600
union ALL SELECT '郑希来',2011,3,2345
union ALL SELECT '郑希来',2011,5,12000
union ALL SELECT '郑希来',2011,4,23456
union ALL SELECT '郑希来',2011,6,4567
union ALL SELECT '郑希来',2011,7,6789
union ALL SELECT '郑希来',2011,8,9998
union ALL SELECT '郑希来',2011,9,34567
union ALL SELECT '郑希来',2011,12,5609

GO

测试结果如下:

SELECT * FROM tb_Income
GO

/*

PName    CYear    CMonth    CMoney
胡一刀    2011    2    5600.00
胡一刀    2011    1    5678.00
胡一刀    2011    3    6798.00
胡一刀    2011    4    7800.00
胡一刀    2011    5    8899.00
胡一刀    2011    8    8877.00
胡一刀    2011    6    7788.00
胡一刀    2011    7    6798.00
胡一刀    2011    10    10000.00
胡一刀    2011    9    12021.00
胡一刀    2011    11    8799.00
胡一刀    2011    12    10002.00
苗人凤    2011    1    3455.00
苗人凤    2011    2    4567.00
苗人凤    2011    3    5676.00
苗人凤    2011    4    5600.00
苗人凤    2011    5    6788.00
苗人凤    2011    6    5679.00
苗人凤    2011    7    6785.00
苗人凤    2011    8    7896.00
苗人凤    2011    9    7890.00
苗人凤    2011    10    7799.00
苗人凤    2011    11    9988.00
郑希来    2011    2    5600.00
郑希来    2011    3    2345.00
郑希来    2011    5    12000.00
郑希来    2011    4    23456.00
郑希来    2011    6    4567.00
郑希来    2011    7    6789.00
郑希来    2011    8    9998.00
郑希来    2011    9    34567.00
郑希来    2011    12    5609.00
*/

现在需要统计2011年的个人总工资,使用Group by 即可:

SELECT PName,sum(Cmoney) as YearMoney from tb_Income
GROUP BY PName
ORDER BY sum(Cmoney) desc

/*
PName    YearMoney
郑希来    104931.00
胡一刀    99060.00
苗人凤    72123.00
*/

现在我们来进行行列转换:

SELECT CYear,胡一刀,苗人凤,郑希来 FROM tb_Income 
PIVOT(sum(CMoney) 
FOR PName IN
 (胡一刀,苗人凤,郑希来)) t

 /*
 CYear    胡一刀    苗人凤    郑希来
2011    5678.00    3455.00    NULL
2011    5600.00    4567.00    5600.00
2011    6798.00    5676.00    2345.00
2011    7800.00    5600.00    23456.00
2011    8899.00    6788.00    12000.00
2011    7788.00    5679.00    4567.00
2011    6798.00    6785.00    6789.00
2011    8877.00    7896.00    9998.00
2011    12021.00    7890.00    34567.00
2011    10000.00    7799.00    NULL
2011    8799.00    9988.00    NULL
2011    10002.00    NULL    5609.00
*/

注意行列已经转换,再汇总,关键是去除干扰列,重新构建新数据集X:

SELECT 胡一刀,苗人凤,郑希来 FROM 
(SELECT PName,CMoney FROM tb_Income) X 
PIVOT(sum(CMoney) 
FOR PName IN
 (胡一刀,苗人凤,郑希来)) t

 /*
    胡一刀    苗人凤    郑希来
    99060.00    72123.00    104931.00
*/

UNPIVOT的示例更简单一些:

生成基本数据:

IF NOT OBJECT_ID('tb_Tel') IS NULL
    DROP TABLE [tb_Tel]

CREATE TABLE [dbo].[tb_Tel](
----[PKID] int primary key identity(101,1),
 [PName] [Nvarchar](20) NOT NULL,
    [Mobile1] [Nvarchar](20) NOT NULL,
    [Mobile2] [Nvarchar](20) NOT NULL,
    [Mobile3] [Nvarchar](20) Not Null
    )
GO
INSERT [dbo].[tb_Tel] 
SELECT '胡一刀','13067894562','13567889667','16767894562'
union ALL SELECT '苗人凤','1507894562','15267889667','15367894562'
union ALL SELECT '郑希来','18067894562','18567889667','18767894562'
GO

结果:

SELECT * FROM tb_Tel

/*
PName    Mobile1    Mobile2    Mobile3
胡一刀    13067894562    13567889667    16767894562
苗人凤    1507894562    15267889667    15367894562
郑希来    18067894562    18567889667    18767894562
*/

行列转换:

SELECT PName,电话类型,电话号码
FROM tb_Tel
UNPIVOT(电话类型 FOR 电话号码 IN (Mobile1,Mobile2,Mobile3) ) p

/*
PName    电话类型    电话号码
胡一刀    13067894562    Mobile1
胡一刀    13567889667    Mobile2
胡一刀    16767894562    Mobile3
苗人凤    1507894562    Mobile1
苗人凤    15267889667    Mobile2
苗人凤    15367894562    Mobile3
郑希来    18067894562    Mobile1
郑希来    18567889667    Mobile2
郑希来    18767894562    Mobile3
*/

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏同步博客

MySQL常用语句

  今天天气很好,大晴天,心情也好好的。就将MySQL常用的语句总结一下,记录在随笔里,也顺便分享分享。日后,这篇随笔我将会持续更新,作为我自己的MySQL语句...

10220
来自专栏跟着阿笨一起玩NET

SQL SERVER 查询死锁

42710
来自专栏Netkiller

新闻数据库分表案例

文章节选自《Netkiller Architect 手札》 6.3. 新闻数据库分表案例 这里我通过一个新闻网站为例,解决分表的问题 避免开发中经常拼接表,我采...

43360
来自专栏冷冷

Quartz依赖数据库表

Quartz的集群部署方案在架构上是分布式的,没有负责集中管理的节点,而是利用数据库锁的方式来实现集群环境下进行并发控制。BTW,分布式部署时需要保证各个节点的...

523100
来自专栏祥子的故事

mysql |那些关于第二大的事

359100
来自专栏沃趣科技

MySQL8.0新特性——invisible indexes

MySQL 8.0版本中新增了invisible indexes(不可见索引)特性,索引被invisible后,MySQL优化器就会忽略该索引(无此特性时需要删...

516110
来自专栏calmound

操作数据表

打开数据库    use 数据库 ---- use mydata 查看数据库    show databases 显示当前用户打开的数据库   SELECT ...

32560
来自专栏ml

行受影响 是什么意思

这就是说明你写的语句没问题,此句实现插入一条name为a kecheng为d fenshu为d  的数据 说明你插入的数据成功... CREATE TABLE ...

33050
来自专栏用户2442861的专栏

mysql 创建 主键索引 唯一索引 全文索引 多列索引 添加索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

78310
来自专栏知无涯

SQL批量删除与批量插入语句

1.4K80

扫码关注云+社区

领取腾讯云代金券