行转列之SQL SERVER PIVOT与用法详解

在数据库操作中,有个别时候大家相见需求完毕“行转列”的须求,举个例子一下的表为某集团的八日收入境况表:

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

小编们先插入一些仿照数据:

INSERT INTO WEEK_INCOME SELECT '星期一',1000UNION ALLSELECT '星期二',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期日',7000

日常大家最日常接受的询问是询问15日中每一天或某几天的收入,例如查询礼拜一至星期六任何的收益:

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 SE奥德赛VEENCORE200第55中学提供了尤其便利的法子,那便是”PIVOT”关系运算符。,以下是采纳PIVOT完结“行转列”的SQL语句

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]FROM WEEK_INCOMEPIVOT( SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]))TBL

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

(v=sql.105).aspx

可是MSDN上的陈述太过分正统严穆,小编看了半天还未有弄精晓什么利用PIVOT,搞不清楚PIVOT里面包车型客车语法的意义。于是又google了众多质地,以致经过地方提到的WEEK_INCOME表例子作了考试,最后搞精晓了其用法。在网络有篇博文解释的很好:,基本上本身要写的正是参照该博文,再拉长自个儿一点私有精通。

要驾驭PIVOT语法,正是要精晓微软何以那样设计PIVOT,但自身相信是现实供给催生设计思路,所以算是我们依然要弄驾驭哪些是“行转列”:

正规境况下的询问结果是那般:

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

行转列后是这么:

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

也正是说,行转列后,原本的某部列的值变做了列名,在此就是原来WEEK列的值“星期二”,”星期三”…”星期六”边做了列名,而大家须求做的另贰个办事正是计量那些列的值

当今组成注释来深入分析一下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里面就只写“星期一”至“星期五”。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。)TBL--别名一定要写

以上是作者对PIVOT的知道,作者尽所能表达出来。但是话说回来,个人的精通的法子也不及,就像是作者起来看了成都百货上千篇博文,都未有搞精通PIVOT用法。结果依然硬的经过例子和人家的博文再增加考虑才弄懂了,所以倘若各位看了本篇之后仍不能分晓,那很符合规律,同盟例子再加上自身动脑筋,稳步的定能通晓。

总结

如上便是那篇文章的全体内容了,希望本文的开始和结果对大家的读书只怕职业有所自然的参阅学习价值,多谢咱们对台本之家的支撑。