行转列：SQL SERVER PIVOT与用法解释

最新推荐文章于 2023-05-18 12:15:57 发布
最新推荐文章于 2023-05-18 12:15:57 发布
阅读量2.7k 收藏 2
点赞数
分类专栏： 数据库资料 MS SQL Server

在数据库操作中，有些时候我们遇到需要实现“行转列”的需求，例如一下的表为某店铺的一周收入情况表：

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

我们先插入一些模拟数据：

复制代码 
INSERT INTO WEEK_INCOME 
SELECT '星期一',1000
UNION ALL
SELECT '星期二',2000
UNION ALL
SELECT '星期三',3000
UNION ALL
SELECT '星期四',4000
UNION ALL
SELECT '星期五',5000
UNION ALL
SELECT '星期六',6000
UNION ALL
SELECT '星期日',7000
复制代码

 

一般我们最经常使用的查询是查询一周中每天或某几天的收入，例如查询周一至周日全部的收入：

SELECT WEEK,INCOME FROM WEEK_INCOME

得到如下的查询结果集：

WEEK           INCOME
星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

 

但是在一些情况下（往往是某些报表中），我们希望在一行中显示周一至周日的收入，这时候查询结果集应该是这样的：

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000     2000     3000     4000     5000     6000     7000

这种情况下，SQL查询语句可以这样写：

复制代码 
SELECT  
SUM(CASE WEEK WHEN '星期一' THEN INCOME END) AS [星期一],
SUM(CASE WEEK WHEN '星期二' THEN INCOME END) AS [星期二],
SUM(CASE WEEK WHEN '星期三' THEN INCOME END) AS [星期三],
SUM(CASE WEEK WHEN '星期四' THEN INCOME END) AS [星期四],
SUM(CASE WEEK WHEN '星期五' THEN INCOME END) AS [星期五],
SUM(CASE WEEK WHEN '星期六' THEN INCOME END) AS [星期六],
SUM(CASE WEEK WHEN '星期日' THEN INCOME END) AS [星期日]
FROM WEEK_INCOME
复制代码

但是，在SQL SERVER 2005中提供了更为简便的方法，这就是"PIVOT"关系运算符。（相反的“列转行”是UNPIVOT），以下是使用PIVOT实现“行转列”的SQL语句

复制代码 
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
FROM WEEK_INCOME
PIVOT
(
    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
)TBL
复制代码

 

 

请参考MSDN中关于PIVOT的用法：

http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx

 

但是MSDN上的描述太过于规范严肃，我看了半天还没弄清楚怎样使用PIVOT，搞不清楚PIVOT里面的语法的含义。于是又google了很多资料，以及通过上面提到的WEEK_INCOME表例子作了试验，最终搞清楚了其用法。在网上有篇博文解释的很好：T-SQL PIVOT語法剖析與實戰，基本上我要写的就是参照该博文，再加上自己一点个人理解。

要理解PIVOT语法，就是要清楚微软为什么这样设计PIVOT，但我相信是现实需求催生设计思路，所以归根到底我们还是要弄清楚什么是“行转列”：

正常情况下的查询结果是这样：

星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

行转列后是这样：

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000    2000    3000    4000    5000    6000    7000

也就是说，行转列后，原来的某个列的值变做了列名，在这里就是原来WEEK列的值“星期一”,"星期二"..."星期日"边做了列名，而我们需要做的另一个工作就是计算这些列的值（这里的“计算”其实就是PIVOT里面的聚合函数(sum,avg等)）

现在结合注释来分析一下PIVOT语法(在这之前最好看看我上面提到博文：T-SQL PIVOT語法剖析與實戰，里面说到的PIVOT语法的三个步骤挺重要)：

复制代码 
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天)
FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误
PIVOT
(
    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤，也是核心的地方，进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”，其中一条的income是1000,另一条income是500，那么在这里使用sum，行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只写“星期一”至“星期五”（注意，in里面是原来week列的值,"以值变列"）。总的来说，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来，就是说：将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列，这些列的值取income的总和。
)TBL--别名一定要写
复制代码

 

以上是我对PIVOT的理解，我尽所能表达出来。不过话说回来，个人的理解的方式也不同，就如我开始看了很多篇博文，都没有搞清楚PIVOT用法。结果还是硬的通过例子和别人的博文再加上思考才弄懂了，所以如果各位看了本篇之后仍不能理解，那很正常，配合例子再加上自己思考，慢慢的定能理解。

以下脚本可以实现动态多栏“透视表”：
declare @week_tmp nchar(20), @select_str nchar(100)
declare pivot_cursor cursor for select distinct week from week_income
open pivot_cursor
fetch next from pivot_cursor into @week_tmp
while @@FETCH_STATUS=0
begin
set @select_str=isnull(rtrim(@select_str)+',','')+'case week when '''+isnull(rtrim(@week_tmp),'')+''' then income end'+isnull(rtrim(' as '+@week_tmp),'')
fetch next from pivot_cursor into @week_tmp
end
close pivot_cursor
deallocate pivot_cursor
set @select_str='select '+rtrim(@select_str)+' from week_income'
exec(@select_str)
当然，我们也可以设法让Pivot脚本动态(第2方案)。只需改两行，其他不变。
......
set @select_str=isnull(rtrim(@select_str)+',','')+isnull(rtrim(@week_tmp),'') --只取出横向透视表多栏名
......
set @select_str='select '+rtrim(@select_str)+' from week_income Pivot (sum(income) for week in ('+rtrim(@select_str)+')) pvt' --实现动态Pivot脚本
......
上述脚本在MSSQL2005验证通过，ORACLE可能需要适当修改(但不支持Pivot，只能第1种方案)，如：+号、isnull函数等。

http://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html

p.s

看了网上的例子，以为只能将纯数字的进行行列转换，但是经过测试，如果表内数据不是数字，也可以进行行转列

另外 此方法只适用于 2005以上数据库，如果是2000的库文件挂接到SQL 2005以上版本的库上执行，会出现如下报错

.....SET COMPATIBILITY_LEVEL....You may need to set the compatibility level of the current database to a higher value to enable this feature.....

其原因就是 2000的库虽然挂接到2005以上的Server上，但是库结构的兼容级别并没有变。

所以 需要 设置数据库兼容级别到 2005以上才行

设置方法：

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }

参数： database_name

要修改的数据库的名称。

COMPATIBILITY_LEVEL { 90 | 100 | 110 }

要使数据库与之兼容的 SQL Server 版本。 该值必须为下列值之一：

90 = SQL Server 2005

100 = SQL Server 2008 和 SQL Server 2008 R2

110 = SQL Server 2012

注释：

对于所有 SQL Server 2012 安装，默认的兼容级别都为 110。 除非 model 数据库有更低的兼容级别，否则 SQL Server 2012 中创建的数据库会设置为该级别。 在将数据库从 SQL Server 的任何早期版本升级到 SQL Server 2012 时，如果数据库的兼容级别不在 90 以下，则该数据库将保留其现有的兼容级别。 升级兼容级别低于 90 的数据库会将数据库的兼容级别设置为 90。 这既适用于系统数据库也适用于用户数据库。 使用 ALTER DATABASE 可更改数据库的兼容级别。 若要查看数据库的当前兼容级别，请查询 sys.databases 目录视图中的 compatibility_level 列。

利用兼容级别获得向后兼容

兼容级别只影响指定数据库的行为，而不影响整个服务器的行为。 兼容级别只实现与 SQL Server 的早期版本保持部分向后兼容。 通过将兼容级别用作临时性的迁移辅助工具，可解决相关兼容级别设置控制的行为之间存在的版本差异问题。 如果现有 SQL Server 应用程序受到 SQL Server 2012 中行为差异的影响，请对该应用程序进行转换，使之能正常运行。 然后使用 ALTER DATABASE 将兼容级别更改为 100。 数据库的新兼容性设置将在该数据库下次成为当前数据库（无论是在登录时作为默认数据库还是在 USE 语句中指定）时生效。

最佳做法

如果在用户连接到数据库时更改兼容级别，可能会使活动查询产生不正确的结果集。 例如，如果在编写查询计划时兼容级别发生更改，则编写后的计划可能同时基于旧的和新的兼容级别，从而造成计划不正确，并可能导致结果不准确。 此外，如果将计划放在计划缓存中供后续的查询重用，则问题可能更加复杂。 为了避免查询结果不准确，建议您使用以下过程来更改数据库的兼容级别：

  1. 通过使用 ALTER DATABASE SET SINGLE_USER，将数据库设置为单用户访问模式。

  2. 更改数据库的兼容级别。

  3. 通过使用 ALTER DATABASE SET MULTI_USER，将数据库设为多用户访问模式。

  4. 有关设置数据库访问模式的详细信息，请参阅 ALTER DATABASE (Transact-SQL)。

兼容级别和存储过程

执行某一存储过程时，该存储过程将使用定义它的数据库的当前兼容级别。 在更改某一数据库的兼容性设置时，该数据库的所有存储过程都将随之自动重新编写。

http://technet.microsoft.com/zh-cn/library/bb510680.aspx


实际操作例子：

创建表：
CREATE TABLE [dbo].[LabelPropertyValue](

[id] [int] IDENTITY(1,1) NOT NULL,
[LabelProid] [int] NULL,
[FaxDocListID] [int] NULL,
[Propertyvalue] [nvarchar](255) NULL,
 CONSTRAINT [PK__LabelPro__3213E83F44160A59] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

测试数据：

id           LabelProid           FaxDocListID           Propertyvalue
1415           2           1200           2013-05-18
1416           3           1200           MU222
1417           7           1200           到达
1418           4           1200           虹桥机场
1419           5           1200           T2
1420           6           1200                      
1421           9           1200           徐XX
1422           10           1200           市部长
1423           11           1200           2013-05-18
1424           12           1200           2045
1425           14           1200           5059
1426           15           1200           服务完成
1427           17           1200           VVIP
1428           2           1198           2013-05-18
1429           3           1198           MU5555
1430           7           1198           出发
1431           4           1198           浦东机场
1432           5           1198           T1
1433           6           1198                      
1434           9           1198           郑XXX
1435           10           1198           市记
1436           11           1198           2013-05-18
1437           12           1198           0900
1438           14           1198           5064
1439           15           1198           服务取消
1440           17           1198           VVIP
1550           15           1201           服务完成
NULL           NULL           NULL           NULL 

如果是 

select [2] as '日期',* from 
(select Propertyvalue,LabelProid from LabelPropertyValue
where   (FaxDocListID = 1200) OR
                      (FaxDocListID = 1201) OR
                      (FaxDocListID = 1198)
) a
PIVOT
(
max(Propertyvalue) for LabelProid in ([2],[3],[7],[4],[5],[6],[9],[10],[11],[12],[14],[15],[17])
)b

结果为 


如果是

select [2] as '日期',* from 
(select FaxDocListID,Propertyvalue,LabelProid from LabelPropertyValue
where   (FaxDocListID = 1200) OR
                      (FaxDocListID = 1201) OR
                      (FaxDocListID = 1198)
) a
PIVOT
(
max(Propertyvalue) for LabelProid in ([2],[3],[7],[4],[5],[6],[9],[10],[11],[12],[14],[15],[17])
)b
order by FaxDocListID desc

结果为 



wangqi0079
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
行转列之SQL SERVER PIVOT与用法详解
12-16
在数据库操作中，有些时候我们遇到需要实现“行转列”的需求，例如一下的表为某店铺的一周收入情况表： WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据： INSERT INTO WEEK_INCOME SELECT ...
SQL Server行列转换
问心一剑的博客
08-21 1万+
本文主要分析了SQL Server数据库中行列转换函数PIVOT和UNPIVOT的使用方法。
【SQLSERVER】行转列方法
寒冰的暖
04-13 2951
1 使用聚合函数巧妙解决，可以用max、sum等。2 使用pivot函数。3.行转列，多列，参考。
sql中pivot函数的使用
qingwenc的博客
09-03 9273
1.数据的创建 create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int) --VendorId 供应商ID, --IncomeDay 收入时间 --IncomeAmount 收入金额 insert into DailyIncome values ('SPIKE', 'FRI', 100) insert into DailyIncome values ('SPIKE', 'MON',
SqlServer行转列&列转行
热门推荐
m0_49844045的博客
01-06 2万+
2、SQLServer 2.1、行转列 -- 第一种方法 select name, max(case cource when '语文' then score else 0 end) as chinese, max(case cource when '数学' then score else 0 end) as math, max(case cource when '英语' then score else 0 end) as english, max(case cource when '物理' then sc
sqlserver行转列
t112358的专栏
02-01 821
SQL Server 使用unpivot/pivot行转列
sql server通过pivot对数据进行行列转换的方法
09-08
主要介绍了sql server通过pivot对数据进行行列转换的方法,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
SQLServer行列互转实现思路（聚合函数）
09-10
主要介绍了SQLServer行列互转实现思路，使用聚合函数pivot/unpivot实现行列互转，感兴趣的小伙伴们可以参考一下
枢纽表达式PIVOT和UNPIVOT的简单用法,sql server 2005，列转成行
02-25
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
SQL server中转置函数（列转行PIVOT()函数、行转列UNPIVOT()函数）
蓝星部队的博客
03-18 1万+
首先，通过两个表格说明本次要讲解的为什么要使用这两个函数？
SqlServer行转列和列转行
05-24
SqlServer如何进行行转列和列转行方法
sql 行专列 列转行 普通行列转换
05-27
NULL 博文链接：https://xkxjy.iteye.com/blog/602387
SQL Server行转列使用
03-17
NULL 博文链接：https://zmx.iteye.com/blog/570073
Sqlserver行转列pivot()
miaomiaotiaopi的博客
08-02 1万+
sqlserver行转列
SQL SERVER 行转列 PIVOT 用法及解释
冰零界点
01-31 1万+
在数据库操作过程中，偶尔会使用到《行转列》查询；下面介绍Sql server 中常用的行转列操作； 1.语法 PIVOT用于将列值旋转为列名（即行转列），在SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column）FOR pivot_column IN (<column_lis...
SQL基础之pivot和unpivot
weixin_45789169的博客
11-17 308
SQL基础之pivot和unpivot的用法 pivot用于行转列，unpivot用于列转行
SQL中PIVOT的用法
whaxrl的专栏
02-17 5189
PIVOT实例   1. 建表 建立一个销售情况表，其中，year字段表示年份，quarter字段表示季度，amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。   CREATE TABLE SalesByQuarter ( year INT, -- 年份 quarter CHAR(2), --
关于SQL中PIVOT函数的使用方法
qq_61820657的博客
09-18 1万+
这篇文章给大家分享的是“pivot函数是什么，具体用法是怎样的”，希望这篇文章对你一定的参考价值
SQL server Pivot函数 行转列效果
2203_75593971的博客
05-18 1389
需要注意的是，在使用PIVOT函数时，FOR子句中的条件（[1]、[2]、[3]、[4]）必须是固定的，不能通过参数传入。它可以将行中的某些列值转换为新的列，同时保留其他行的列。行转列指的是将数据行中的某个或某些列的值，转换为新的列，从而实现“一列转多列”的操作。总的来说，这条查询语句利用了SQL Server的内置函数，通过简单的代码实现了行转列的操作，对于需要展示某些列值的情况非常适用。无论使用哪种方法，行转列的操作都可以提高数据概览和分析的效率和效果，并更好地实现数据的可视化和报表展示。
sql server行转列
最新发布
09-12
在 SQL Server 中，将行转换为列可以使用 PIVOT 操作符或使用 CASE 表达式来实现。下面是两种常用的方法： 1. 使用 PIVOT 进行行转列： ```sql SELECT [列1], [列2], [列3], ... FROM ( SELECT 列名, 值 FROM ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值