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

转载 2013年12月03日 15:21:03

【转】原文地址:http://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html

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

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

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--别名一定要写
复制代码

 


t-sql中pivot用法(行列转换)

从另一张表找的3974行是张三,8319行是李四,3051行是王五; 从stu_score查id,stu_id两列,聚合求出 得出张三,李四,王五在stu_score中的记录条数  s...
  • CsethCRM
  • CsethCRM
  • 2014年09月11日 08:49
  • 3294

SQL中PIVOT 行转列和UNPIVOT列转行实例讲解

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。 ...
  • u010541307
  • u010541307
  • 2016年10月22日 21:09
  • 4511

SQL 2008行列转换的pivot--产生动态列

 突然间发现,已经好久没有写博客了,也好久没用SQLServer进行开发了。由于目前项目开发的原因,对于SQL Server才重视起来。发现SQL Server也引进了不少新的东西,现将一个不错...
  • langcai1981
  • langcai1981
  • 2014年01月21日 10:42
  • 7842

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

我在网上看到了 pivot的用法,我于是复制下了,以便我下次查资料,自己也可以相当于回顾一下 列子SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这...
  • qq1010556575
  • qq1010556575
  • 2013年08月13日 13:37
  • 460

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

在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先...
  • wangqi0079
  • wangqi0079
  • 2013年10月15日 16:25
  • 1220

sql server 行转列 Pivot UnPivot

PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )...
  • a389483637
  • a389483637
  • 2014年06月06日 09:52
  • 886

sql server行转列 Pivot UnPivot

SQL Server中行列转换 Pivot UnPivot  好东西啊。。。 PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实...
  • kayv
  • kayv
  • 2011年10月13日 13:25
  • 402

Sql Server 2005 行转列的实现 (横排)

  • 2009年09月17日 11:24
  • 161KB
  • 下载

SQL Server数据行转列案例实践

数据库某表行转列案例实践 --第一步,创建测试表[StudentsScore]及测试数据 CREATE TABLE [dbo].[StudentsScore](  [Student] [...
  • tongyiyi
  • tongyiyi
  • 2013年02月26日 10:48
  • 362

SQL SERVER【1】——行转列

行列转换在编程中用的比较多,故稍微总结了下,具体看下面: 一.创建测试数据表 /*创建册数数据表【订单表】*/ USE [TEST] GO /****** Object: Table ...
  • fmx121586
  • fmx121586
  • 2012年12月03日 17:10
  • 6222
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:行转列:SQL SERVER PIVOT与用法解释
举报原因:
原因补充:

(最多只允许输入30个字)