发布：2018/12/19 17:50:24作者：管理员 来源：本站 浏览次数：1397
ylbtech-SQL Server：使用 PIVOT 行转列和 UNPIVOT 列转行
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT
通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与
PIVOT 执行相反的操作，将表值表达式的列转换为列值。
【注】对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。
| A，PIVOT 语法 返回顶部 |
1、
PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明，请参阅 FROM (Transact-SQL)。
以下是带批注的 PIVOT 语法。
SELECT <非透视的列>, [第一个透视的列] AS <列名称>, [第二个透视的列] AS <列名称>, ... [最后一个透视的列] AS <列名称>, FROM (<生成数据的 SELECT 查询>) AS <源查询的别名> PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列]) ) AS <透视表的别名> <可选的 ORDER BY 子句>;
2、
| B，PIVOT 示例1返回顶部 |
0、
示例脚本源
1、
P1、如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据
1-1、
--A1、传统 case 方法 select username as '姓名' ,max(case [subject] when '语文' then score else 0 end) as '语文' ,max(case [subject] when '数学' then score else 0 end) as '数学' ,max(case [subject] when '英语' then score else 0 end) as '英语' ,max(case [subject] when '生物' then score else 0 end) as '生物' from StudentScores group by username;
1-2、
-- A2：PIVOT 方法 select username as '姓名',[语文],[数学],[英语],[生物] from StudentScores a PIVOT ( max(a.score) for a.subject in([语文],[数学],[英语],[生物]) )b;
1-3、查询结果
1-4、
2、
| C，UNPIVOT 示例1返回顶部 |
0、
go -- ========================== -- 工程详细表，ByYuanbo -- ========================== -- drop table ProjectDetail; create table ProjectDetail ( projectName varchar(20), --功能名称 overseaSupply int, --海外供应商供给数量 nativeSupply int, --国内供应商供给数量 southSupply int, --南方供应商供给数量 northSupply int --北方供应商供给数量 ); go -- 01、添加测试数据 insert into ProjectDetail(projectName,overseaSupply,nativeSupply,southSupply,northSupply) values('A', 100, 200, 50, 50); insert into ProjectDetail(projectName,overseaSupply,nativeSupply,southSupply,northSupply) values('B', 200, 300, 150, 150); insert into ProjectDetail(projectName,overseaSupply,nativeSupply,southSupply,northSupply) values('C', 159, 400, 20, 320); go -- 02、查询数据 select * from ProjectDetail; -- P1：查询项目每个供应商的供给数量？ -- A1：UNPIVOT 方法 select b.projectName,b.supplier,b.supllyNumber from ( select projectName,overseaSupply,nativeSupply,southSupply,northSupply from ProjectDetail) a UNPIVOT ( supllyNumber for supplier in(overseaSupply,nativeSupply,southSupply,northSupply) )b;
1、
-- P1：查询项目每个供应商的供给数量？ -- A1：UNPIVOT 方法 select b.projectName,b.supplier,b.supllyNumber from ( select projectName,overseaSupply,nativeSupply,southSupply,northSupply from ProjectDetail) a UNPIVOT ( supllyNumber for supplier in(overseaSupply,nativeSupply,southSupply,northSupply) )b;
2、
| D，PIVOT 示例2返回顶部 |
1、建立一个销售情况表，其中，year字段表示年份，quarter字段表示季度，amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。
2、
示例脚本源
3、
| E，返回顶部 |
| F，返回顶部 |
| G，相关资源返回顶部 |
1、官方示例
1,
H，
