Transact-SQL 使用 PIVOT 和 UNPIVOT 实现行与列之间的转化

I:使用 PIVOT 和 UNPIVOT 命令的SQL Server版本要求

1.数据库的最低版本要求为 SQL Server 2005 或 更高

2.必须将数据库的兼容级别设置为 90 或 更高

3.查看我的数据库版本及兼容级别

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入
print @@version
运行之后在我的本机上得到

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
    Apr  2 2010 15:53:02
    Copyright (c) Microsoft Corporation
    Express Edition with Advanced Services on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息
 
在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习

II:使用 PIVOT 实现数据表的行转列

1.在这里我们先构建一个测试数据表(这里使用的是临时表,以方便我们在退出会话的时候自动删除表及其数据)

首先我们先设计一个表架构为 #Student { 学生编号[PK],  姓名, 性别, 所属班级 } 的表,然后编写如下T-SQL

--创建临时表(仅演示,表结构的不合理还请包涵)
CREATETABLE #Student (
   [学生编号]INTIDENTITY(1, 1) PRIMARYKEY,
   [姓名]NVARCHAR(20),
   [性别]NVARCHAR(1),
   [所属班级]NVARCHAR(20)
); 

--给临时表插入数据   
INSERTINTO #Student (
   [姓名], [性别], [所属班级]
)
SELECT'李妹妹', '女', '初一 1班'UNIONALL
SELECT'泰强', '男', '初一 1班'UNIONALL
SELECT'泰映', '男', '初一 1班'UNIONALL
SELECT'何谢', '男', '初一 1班'UNIONALL
SELECT'李春', '男', '初二 1班'UNIONALL
SELECT'吴歌', '男', '初二 1班'UNIONALL
SELECT'林纯', '男', '初二 1班'UNIONALL
SELECT'徐叶', '女', '初二 1班'UNIONALL
SELECT'龙门', '男', '初三 1班'UNIONALL
SELECT'小红', '女', '初三 1班'UNIONALL
SELECT'小李', '男', '初三 1班'UNIONALL
SELECT'小黄', '女', '初三 2班'UNIONALL
SELECT'旺财', '男', '初三 2班'UNIONALL
SELECT'强强', '男', '初二 1班'; 

 

以下是查询的结果

学生编号	姓名	性别	所属班级
1	李妹妹	女	初一 1班
2	泰强	男	初一 1班
3	泰映	男	初一 1班
4	何谢	男	初一 1班
5	李春	男	初二 1班
6	吴歌	男	初二 1班
7	林纯	男	初二 1班
8	徐叶	女	初二 1班
9	龙门	男	初三 1班
10	小红	女	初三 1班
11	小李	男	初三 1班
12	小黄	女	初三 2班
13	旺财	男	初三 2班
14	强强	男	初二 1班

2.查询各班级的总人数

SELECT 
   [所属班级]AS[班级],
   COUNT(1) AS[人数]
FROM #Student 
GROUPBY[所属班级]
ORDERBY[人数]DESC

 

班级	人数
初二 1班	5
初一 1班	4
初三 1班	3
初三 2班	2

好了,在这里我希望把上面的 表 { 班级, 人数 } 由 班级[行] 的显示转换为 班级[列] 的显示格式!

在此你会看到第一个PIVOT示例~_~

是否很期待??

3.编写第一个PIVOT示例

SELECT 
   '班级总人数:'AS[总人数],
   [初一 1班], [初一 2班],
   [初二 1班],
   [初三 1班], [初三 2班]
FROM (
   SELECT 
      [所属班级]AS[班级], 
      [学生编号]
   FROM #Student 
) AS[SourceTable]
PIVOT (
   COUNT([学生编号])
   FOR[班级]IN (
      [初一 1班], [初一 2班], 
      [初二 1班],
      [初三 1班], [初三 2班]
   )
) AS[PivotTable]

在结果表中我们看到了对于不存在的班级 初一 2班 它的总人数为0, 这符合我们预期的结果!

解释:使用POVIT首先你需要在FROM子句内定义2个表

A.一个称为源表(SourceTable)

B.另一个称为数据透视表(PivotTable)

语法
SELECT

    <未透视的列>,

    [第一个透视列] AS <列别名>,

    [第二个透视列] AS <列别名>,

    ...

    [最后一个透视列] AS <列别名>

FROM (

    <SELECT查询>

) AS <源表>

PIVOT (

    <聚合函数>(<列>)

    FOR [<需要转换为行的列>] IN (

        [第一个透视列], [第二个透视列],

        ...

       [最后一个透视列]

    )

) AS <数据透视表>

<可选的ORDER BY子句>;

以上的PIVOT子句内的第1…n个透视列的值均为 需要转换为行的列 的常量值,需要用[]括起,支持GUID,字符串及各种数字!
具体Technet地址为: http://technet.microsoft.com/zh-cn/library/ms177410(SQL.100).aspx

4.下面演示一个较为高级的行转列的应用示例

--使用 PIVOT 查询班级内的男女学生人数及总人数
SELECT 
   [所属班级]AS[班级],
   [男]AS[男生人数],
   [女]AS[女生人数],
   [男]+[女]AS[总人数]
FROM (
   SELECT[学生编号], [所属班级], [性别]FROM #Student
) AS[SourceTable]
PIVOT (
   COUNT([学生编号])
   FOR[性别]IN (
      [男], [女]
   )
) AS[PivotTable]
ORDERBY[总人数]DESC

III:使用 UNPIVOT 实现的功能其实与PIVOT恰恰相反

1.语法同PIVOT.但是UNPIVOT的子句没有聚合函数

SELECT

    <未逆透视的列>,

    [合并后的列] AS <列别名>,

    [行值的列名] AS <列别名>

FROM (

    <SELECT查询>

) AS <源表>

UNPIVOT (

    <行值的列名>

    FOR <将原来多个列合并到单个列的列名> IN (

        [第一个合并列], [第二个合并列],

        ...

       [最后一个合并列]

    )

) AS <数据逆透视表>

<可选的ORDER BY子句>;

2.看上面的语法感觉很浮云,不怕,这里带例子(继续使用II中用到的PIVOT表)

--源表

SELECT 
   '班级总人数:'AS[总人数],
   [初一 1班], [初一 2班],
   [初二 1班],
   [初三 1班], [初三 2班]
   INTO #PivotTable --为了使表达意图更清晰,我把PIVOT处理后的表放到一个临时表当中
FROM (
   SELECT 
      [所属班级]AS[班级], 
      [学生编号]
   FROM #Student 
) AS[SourceTable]
PIVOT (
   COUNT([学生编号])
   FOR[班级]IN (
      [初一 1班], [初一 2班], 
      [初二 1班],
      [初三 1班], [初三 2班]
   )
) AS[PivotTable]

将多个列合并到单个列的转换的语句!!!

--结果
SELECT 
   [班级], [总人数]
FROM (
   SELECT 
      [初一 1班], [初一 2班],
      [初二 1班],
      [初三 1班], [初三 2班]
   FROM 
      #PivotTable
) AS[s]
UNPIVOT (
   [总人数]
   FOR[班级]IN (
      [初一 1班], [初一 2班],
      [初二 1班],
      [初三 1班], [初三 2班]
   )
) AS[un_p]

在这里也写个比较高级的希望各位DBA莫笑!
觉得好的请[推荐]一下下,本人时间有限,未能逐一回复,请见晾!首先谢谢各位看过本文的朋友!

SELECT 
   [所属班级]AS[班级],
   [男]AS[男生人数],
   [女]AS[女生人数],
   [男]+[女]AS[总人数]
   INTO #PivotTable2 --放到临时表方便查询
FROM (
   SELECT[学生编号], [所属班级], [性别]FROM #Student
) AS[SourceTable]
PIVOT (
   COUNT([学生编号])
   FOR[性别]IN (
      [男], [女]
   )
) AS[PivotTable]
ORDERBY[总人数]DESC

 

SELECT 
   [班级], 
   [男生或女生人数],
   [性别],
   [总人数]
FROM (
   SELECT[班级], [男生人数], [女生人数], [总人数]FROM #PivotTable2
) AS[s]
UNPIVOT (
   [男生或女生人数]
   FOR[性别]IN (
      [男生人数],
      [女生人数]
   )
) AS[un_p]

或者将 性别 和 人数合并到一个列当中:

SELECT 
   [班级], 
   [性别]+': '+CAST([男生或女生人数]ASNVARCHAR(1)) AS[男生或女生人数],
   [总人数]
FROM (
   SELECT[班级], [男生人数], [女生人数], [总人数]FROM #PivotTable2
) AS[s]
UNPIVOT (
   [男生或女生人数]
   FOR[性别]IN (
      [男生人数],
      [女生人数]
   )
) AS[un_p]

IV:网友提问解答

就在2012-08-07突然收到一个园友的短消息问问题，正好今天上午思路一变有了答案就答复了他！

他的问题是这样的把

Id	SName	Course1	Course2	Course3
1	张三	80	60	70
2	李四	60	70	57
3	王五	85	87	94

变成下表

Course	张三	李四	王五
Course1	80	60	85
Course2	60	70	87
Course3	70	57	94

以下是我简陋的代码回复...

select 1 as Id, N'张三' as SName, 80 as Course1, 60 as Course2, 70 as Course3 union all
select 2, N'李四', 60, 70, 57 union all
select 3, N'王五', 85, 87, 94

;with cte1 as (
    select 1 as Id, N'张三' as SName, 80 as Course1, 60 as Course2, 70 as Course3 union all
    select 2, N'李四', 60, 70, 57 union all
    select 3, N'王五', 85, 87, 94
), 
cte2 as (
    select SName, Course1 as fs, N'Course1' as Course from cte1 union all
    select SName, Course2, N'Course2' from cte1 union all
    select SName, Course3, N'Course3' from cte1
)
select
    Course, [张三], [李四], [王五]
from (
    select * from cte2 group by Course, SName, fs
) src
pivot (
    max(fs)
    for SName in (
        [张三], [李四], [王五]
    )
) piv

本文已结束!!