SQL基础教程之行转列Pivot函数

来源:脚本之家  责任编辑:小易  

你这是Oracle数据库,给你看看我写的: 列转行 录入经营范围时候会遇到列传行的问题解决方案如下: 在temp1 表有一下字段内容: 一条sql转化列传行效果 select a,zj,zjname from temp1 unpivot(zjname for zj in(b,c,d,e,f,g,h,i,j)); 行转列:

前言

未来的一个月时间中,会总结一系列SQL知识点,一次只总结一个知识点,尽量说明白,下面来说说SQL 中常用Pivot 函数(这里是用的数据库是SQLSERVER,与其他数据库是类似的,大家放心看就好)

1、我用的是SQL Server2012 EXpress版本2、首先建了个表new_SC,插入数据,查询效果如图:3、然后用pivot进行行列转换:4、结果就如上图,没有达到效果,我又换了个写法:5、我想问一下为什么我用pivot不行呢?我也反复查过资料,pivot语句没有

让我们先从一个虚构的场景中来着手吧

SELECT . FROM PIVOT ( aggregate-function() FOR IN (, ,, ) ) AS WHERE ..

万国来朝,很多供应商每天都汇报各自的收入情况。先来创建一个DailyIncome 表

--楼主 这个是典型的行转列 --脚本如下 select 星期一 , 星期二, 星期三, 星期四, 星期五, 星期六 , 星期日 from test pivot (sum(ine) for week in(星期一 , 星期二, 星期三, 星期四, 星期五, 星期六 , 星期日)) as tt --如果解决了楼主的问

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int) --VendorId 供应商ID, --IncomeDay 收入时间 --IncomeAmount 收入金额

SELECT * FROM (SELECT key,str FROM table t) pivot (max(str) for key in ('hyl','ls','ryhl','yqwd'))

紧接着来插入数据看看

自己住过8层有蚊子,住过5层有蚊子,住过13层有蚊子!再高的没有住过了!22楼还没经历过夏天,所以不知道有没有蚊子!在家里8层楼,不但有蚊子还有老鼠!你没有看错有老鼠!老鼠牛逼的顺着管道爬上了8层!我们家原来防盗网上养的仙人球它都啃!不知道它觉不觉得扎嘴!在学校住的是5楼,也有蚊子,但学校不让点蚊香,怕着火,但是我们可没那么乖,有时候蚊子多了同样的点,只不过我们会小心点,放在中间,周围都没有什么可燃物的地方!夏天热,我们一般也会开空调,所以没有怎么被蚊子咬!现在住13楼,夏天热,开着窗睡!有时候忘了点蚊香,现在的后果是手上、腿上全是红点点!感觉这里的蚊子都开挂了,跑的贼快!拍都拍不上!

(留意看下,有的供应商某天中会有多次收入,应该是分批进账的)

射程最远的应该是瘟疫之源(老鼠),850码力压群雄!TOP1.瘟疫之源850码(550+300)老鼠的初始射程尽管只有550,这在ADC中只能算是中等,但当老鼠6级升级大招之后,那就是质的飞跃,大招给老鼠增加了整整300的射程,达到850!要知道防御塔的射程才825,老鼠开大可以不受攻击的点塔,太可怕了。而且deft去年给我们展示了以眼作为攻击跳板用溅射来提供伤害,让人叹为观止。目前飓风流老鼠盛行于召唤师峡谷之中,一旦老鼠发育良好,团战起来真的是毁天灭地呀,第一射程当之无愧!TOP2、大嘴大嘴是开技能射程第二远的英雄,大嘴5级W之后可以拥有710的射程,更有附加的百分比伤害,是一代肉盾杀手,所

insert into DailyIncome values ('SPIKE', 'FRI', 100) insert into DailyIncome values ('SPIKE', 'MON', 300) insert into DailyIncome values ('FREDS', 'SUN', 400) insert into DailyIncome values ('SPIKE', 'WED', 500) insert into DailyIncome values ('SPIKE', 'TUE', 200) insert into DailyIncome values ('JOHNS', 'WED', 900) insert into DailyIncome values ('SPIKE', 'FRI', 100) insert into DailyIncome values ('JOHNS', 'MON', 300) insert into DailyIncome values ('SPIKE', 'SUN', 400) insert into DailyIncome values ('JOHNS', 'FRI', 300) insert into DailyIncome values ('FREDS', 'TUE', 500) insert into DailyIncome values ('FREDS', 'TUE', 200) insert into DailyIncome values ('SPIKE', 'MON', 900) insert into DailyIncome values ('FREDS', 'FRI', 900) insert into DailyIncome values ('FREDS', 'MON', 500) insert into DailyIncome values ('JOHNS', 'SUN', 600) insert into DailyIncome values ('SPIKE', 'FRI', 300) insert into DailyIncome values ('SPIKE', 'WED', 500) insert into DailyIncome values ('SPIKE', 'FRI', 300) insert into DailyIncome values ('JOHNS', 'THU', 800) insert into DailyIncome values ('JOHNS', 'SAT', 800) insert into DailyIncome values ('SPIKE', 'TUE', 100) insert into DailyIncome values ('SPIKE', 'THU', 300) insert into DailyIncome values ('FREDS', 'WED', 500) insert into DailyIncome values ('SPIKE', 'SAT', 100) insert into DailyIncome values ('FREDS', 'SAT', 500) insert into DailyIncome values ('FREDS', 'THU', 800) insert into DailyIncome values ('JOHNS', 'TUE', 600)

缅甸除了在仰光公盘标的料,其他都属于走私翡翠。由于各种原因,缅甸走私翡翠越来越多,被查的走私料,有的归缅甸政府再上公盘标,另外如果刚被查,货主得知消息了,检查站还没上报,就可以找关系花钱赎回来,这个钱的最后归宿就呵呵了。热水塘检查站刚回复的时候,为了站点的功绩,查了不少啊,要是查不出来,就没必要存在了。现在各方面关系到位了,呵呵,没走私了,或者少了,只能呵呵了。

让我们先来看看前十行数据:

李清云(李庆远)被称作是全球最长寿的人,他的寿命竟然达到了256岁。李清云出生于1677年,逝世于1933年。对于李清云的长寿故事,报纸和电视台也曾多次进行过报道。那么,你知道吗?李清云活到256岁是真是假?有很大一部分人认为李清云的年龄可信度较低,原因在于李清云并不是土生土长的四川人,在开县前的经历和他的实际年龄仅是凭借他自己所说,无从考察。对于李清云早年的事情更是无人知晓。根据已有的资料来看,李清云在四川出生,也在四川逝世,在他十岁的时候就前往西藏、甘肃、陕西、安南和暹罗等地采药,之后他的事情就基本没有了记载。而他的职业是以给人看病和出售草药为主,李清云也曾说自己并不是生在1677年,而是

select top 10 * from DailyIncome

如图所示:

DailyIncome

虽然数据是能够完全给展示了,但好像一眼望去不能得到对我们用处更大的信息,比如说我们想得到每个供应商的每天的总收入,这时我们应该做一些数据形式的转变了,平常的所用的是这样的。

select VendorId , sum(case when IncomeDay='MoN' then IncomeAmount else 0 end) MON, sum(case when IncomeDay='TUE' then IncomeAmount else 0 end) TUE, sum(case when IncomeDay='WED' then IncomeAmount else 0 end) WED, sum(case when IncomeDay='THU' then IncomeAmount else 0 end) THU, sum(case when IncomeDay='FRI' then IncomeAmount else 0 end) FRI, sum(case when IncomeDay='SAT' then IncomeAmount else 0 end) SAT, sum(case when IncomeDay='SUN' then IncomeAmount else 0 end) SUN from DailyIncome group by VendorId

得到如下的结果:

case when结果

如果大家仔细看结果的话,会有这样的发现,这是把VendorID进行了分组,并且对于每组中IncomeDay这一列中的值都变成了新的列名字,然后对IncomeAmount进行求和操作。

这样写可能是有些麻烦,别着急,我们用Pivot函数进行行转列试下。

select * from DailyIncome ----第一步 pivot ( sum (IncomeAmount) ----第三步 for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步 ) as AvgIncomePerDay

来解释下,要想用好Pivot函数,应该理解代码注释中的这几步。

第一步:肯定是要明白数据源了,这里是DailyIncome

第二步:要明白要想让哪一列的值做新的列名字

第三步:要明白对于这新的列要求那些值呢?

下面有个练习题目,做之前不要看答案啊

问:对于SPIKE这家供应商来说,每天最大的入账金额。

select * from DailyIncome pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay where VendorId in ('SPIKE')

参考链接如下:

1.Pivot tables in SQL Server. A simple sample

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

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

扩展阅读,根据您访问的内容系统为您准备了以下内容,希望对您有帮助。

sql动态多行转列,PIVOT怎么能转两列?

楼上那个用过sqlserver吗?

PIVOT是支持的!!!!!

不过看你的语法,肯定报错。PIVOT最好基于SELECT * 并且先把全部需求的字段转换未列:

select * from

(select CONVERT(varchar(100),年月)+'受理' 栏位,sum(受理数量) 数量 from

 表a

GROUP BY 年月

UNION  ALL

select CONVERT(varchar(100),年月)+'办结'栏位,sum(办结数量) 数量 from

表a

GROUP BY 年月)v

PIVOT ( MAX(v.数量) FOR v.栏位 IN ( [2019-01受理],

[2019-02受理],

[2019-03受理],

[2019-04受理],

[2019-01办结],

[2019-02办结],

[2019-03办结],

[2019-04办结] )) 回答用

Sql server关于PIVOT行转列的问题

select SUM(行的值),ave(行的值) 【,需要检索的属性】【,需要检索的属性】...

from 表名

where 条件表达式

group by 分组属性;追问行转列,然后再统计,数据是按大米的属性去录入的,为啥是这样设计是因为我做了个不定列的excel导入数据库,只能这样设计,一个列是成交额、一个列的成交量以及本月均价,列可以无限多所以用户的属性可以无限加,然后用户产品可能很多,不同用户上传不同的excel,excel模板是我们提供的,就是产品固定,属性固定,前面说的无限加时产品和属性不固定时指不同的excel模板,然后每个模板都是要统计成图2哪个

请教SQL server 中pivot的详细用法及语法规则

SQL server 中使用 PIVOT关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。

PIVOT 语法为SELECT <非透视的列>, [第一个透视的列],...[最后一个透视的列] FROM (<生成数据的 SELECT 查询>)  PIVOT (<聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列],... [最后一个透视的列]) ) AS <透视表的别名> <可选的 ORDER BY 子句>。

扩展资料:

由于SQL Server 2005有了新的PIVOT运算符,就不再需要CASE语句和GROUP BY语句了。(每个PIVOT查询都涉及某种类型的聚合,因此可以忽略GROUP BY语句。)PIVOT运算符能够利用CASE语句查询实现相同的功能,但是可以用更少的代码就实现,而且看起来更漂亮。

PIVOT运算符前面接的一定是一条查询语句。 有与PIVOT 执行相反的运算符UNPIVOT,将表值表达式的列转换为列值。相比于直接通过union来实现列转行,使用UNPIVOT可以让sql语句变得更加简洁。相对而言,理解难度会上升,可读性下降。

sql关于pivot行列转换的问题

--楼主 这个是典型的行转列

--脚本如下

select 星期一 , 星期二, 星期三, 星期四, 星期五, 星期六 , 星期日

from test pivot (sum(income) for week in(星期一 , 星期二, 星期三, 星期四, 星期五, 星期六 , 星期日)) as tt

--如果解决了楼主的问题 请采纳

--有什么疑问可以随时找我追答

我没有调试 等等我调试一下 调试还要建表

语法没有问题 你看看你的income列是不是字符串类型 如果是整型 就没有问题

楼下那是列是动态的情况下 比较合适 这样把问题搞复杂了 

请尊重先回答者

追问提示有语法错误

SQL中对临时表如何用 PIVOT 进行列转换

1、跟是否临时表没有关系。

2、SELECT列表中的转出字段,也要加上[]

3、加上DQ字段

SELECTDQ

    ,[42周]

    ,[43周]

       ,[44周]

   ,[45周]

   ,[46周]

   ,[47周]

FROM#TEMPDBF 

PIVOT( SUM(TEU) FOR 

周 IN ([42周], [43周], [44周], [45周], [46周],[47周]) 

) b追答远程库是MS SQL Server?追问

按您的做法在本地成功了,我相应的在我要设计的远程数据库里使用,死活不行

表内容

运行结果


  • 本文相关:
  • mysql 行转列和列转行实例详解
  • sql语句实现行转列的3种方法实例
  • sqlserver行转列实现思路记录
  • mysql存储过程中使用动态行转列
  • mssql 数据库表行转列,列转行终极方案
  • sql server 2000 行转列的实现(横排)
  • sql查询语句行转列横向显示实例解析
  • sql动态行转列的两种方法
  • table 行转列的sql详解
  • sql行转列和列转行代码详解
  • 数据库删除完全重复和部分关键字段重复的记录
  • sqlserver 2005 和oracle 语法的一点差异小结
  • 在数据库中如何高效的实现订座功能
  • 数据库的设计方法、规范与技巧
  • 深入讲解sql中的字符串拼接
  • 带参数的sql和不带参数的sql存储过程区别
  • sqlserver和oracle中对datetime进行条件查询的一点区别小结
  • 恢复 sql 被注入后的数据代码
  • sql注入之基于布尔的盲注详解
  • sql select distinct的使用方法
  • sql动态多行转列,PIVOT怎么能转两列?
  • Sql server关于PIVOT行转列的问题
  • 请教SQL server 中pivot的详细用法及语法规则
  • sql关于pivot行列转换的问题
  • SQL中对临时表如何用 PIVOT 进行列转换
  • sql怎样将行的值变为列,,,,
  • 通过PIVOT进行行转列,其中栏位部怎么用STUFF来一...
  • 如何用Pivot实现行列转换
  • sql pivot,sql pivot 行列转换问题,按月份分组统...
  • oracle这种数据 pivot或者其他怎么将key做字段 str...
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved