开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server 2008 R2——PIVOT 行转列 以及聚合函数的选择

简介: 原文:SQL Server 2008 R2——PIVOT 行转列 以及聚合函数的选择 ==================================声明================================== 本文原创,转载在正文中显要的注明作者和出处,并保证文章的完整性。
+关注继续查看
原文:SQL Server 2008 R2——PIVOT 行转列 以及聚合函数的选择

==================================声明==================================

本文原创,转载在正文中显要的注明作者和出处,并保证文章的完整性。

未经作者同意请勿修改(包括本声明),保留法律追究的权利。

未经作者同意请勿用于学术性引用。

未经作者同意请勿用于商业出版、商业印刷、商业引用。

本文不定期修正完善,为保证内容正确,建议移步原文处阅读。

本文链接:http://www.cnblogs.com/wlsandwho/p/4423956.html

=======================================================================

看了好长时间的资料,总算是看懂了点。(另外怎样才能把右面的这个长毛的土豆去掉?---->                         对,就是它!)

赶紧记录下来。

=======================================================================

表格和数据。(和http://www.cnblogs.com/wlsandwho/p/4416222.html的相同)

 1 USE tempdb
 2 GO
 3 --------------------------------------------------------------------------
 4 IF OBJECT_ID('Student','U') IS NOT NULL
 5 DROP TABLE Student
 6 GO
 7 
 8 CREATE TABLE Student
 9 (
10     StuID NVARCHAR(8) PRIMARY KEY,
11     Name NVARCHAR(5),
12     EntranceTime DATETIME
13 )
14 GO
15 
16 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20080001','Lily','2008-08-27')
17 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20090002','Lucy','2009-08-26')
18 INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20070003','Jack','2007-08-28')
19 GO
20 --------------------------------------------------------------------------
21 IF OBJECT_ID('SltCourse','U') IS NOT NULL
22 DROP TABLE SltCourse
23 GO
24 
25 CREATE TABLE SltCourse    --SelectiveCourse
26 (
27     ID INT PRIMARY KEY IDENTITY(1,1),
28     StuID NVARCHAR(8),
29     CourseName NVARCHAR(10),
30     Score    INT 
31 )
32 GO
33 
34 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','电脑维修',90)
35 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','剪纸',80)
36 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','市场策划',95)
37 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','信息检索',100)
38 
39 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
40 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
41 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)
42 
43 INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20090002','插花',98)
44 GO

查看一下

1 SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
2 GO

=======================================================================

现在来看这个表格,前三列有很多数据是重复的,而且也不便于阅读。之前使用了row_number函数,这里使用下别的——PIVOT来实现一个行转列。

预期效果。

=======================================================================

静态版。这需要预知要转换成列名的数据。

1 WITH ReportCard 
2 AS(
3 SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
4 )
5 SELECT * FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN([电脑维修],[剪纸],[市场策划],[信息检索],[插花],[刺绣]) ) AS T
6 GO

结果。

当在第三行加上筛选语句时,可以得到指定的某一行。例如:

1 WITH ReportCard 
2 AS(
3 SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid AND s.StuID='20090002'
4 )
5 SELECT * FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN([电脑维修],[剪纸],[市场策划],[信息检索],[插花],[刺绣]) ) AS T
6 GO

结果。

=======================================================================

动态版。先查询出要转为列的行数据,然后设置格式,再拼接字符串。

 1 DECLARE @strCN NVARCHAR(100)
 2 SELECT @strCN=ISNULL(@strCN+',','')+ QUOTENAME(CourseName) FROM  SltCourse GROUP BY CourseName
 3 --PRINT @DCN
 4 
 5 DECLARE @SqlStr NVARCHAR(1000)
 6 SET @SqlStr='
 7 WITH ReportCard 
 8 AS(
 9 SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid 
10 )
11 SELECT * FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN('+@strCN+') ) AS T'
12 EXEC(@sqlstr)
13 GO

=======================================================================

题外话,关于PIVOT中聚合函数的使用。

 

我刚用TSQL不久,行转列都是汇总一些数值类型的数据,比方说,周一到周日的营业收入啦,数理化史地生成绩啦之类的。

这些数值类型的,选择聚合函数时,当然是需要什么选什么啦。

看例子。

 1 USE tempdb
 2 GO
 3 
 4 IF OBJECT_ID('AggregateFuncTest1','U') IS NOT NULL
 5 DROP TABLE AggregateFuncTest1
 6 GO
 7 
 8 CREATE TABLE AggregateFuncTest1
 9 (
10     ID INT IDENTITY(1,1) PRIMARY KEY,
11     DayOfWeek NVARCHAR(3),
12     Income INT
13 )
14 GO
15 
16 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期一',100)
17 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期二',200)
18 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期三',300)
19 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期四',400)
20 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期五',500)
21 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期六',600)
22 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期日',700)
23 GO
24 ----------------------------------------------------------------------
25 SELECT DayOfWeek,Income FROM AggregateFuncTest1
26 GO
27 ----------------------------------------------------------------------
28 WITH Rst AS
29 (
30     SELECT DayOfWeek,Income FROM AggregateFuncTest1
31 )
32 SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] FROM Rst 
33 PIVOT(AVG(Income) FOR DayOfWeek IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) AS T
34 GO
35 ----------------------------------------------------------------------
36 INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期一',1000)
37 GO
38 
39 WITH Rst AS
40 (
41     SELECT DayOfWeek,Income FROM AggregateFuncTest1
42 )
43 SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] FROM Rst 
44 PIVOT(AVG(Income) FOR DayOfWeek IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) AS T
45 GO
46 ----------------------------------------------------------------------
47 DROP TABLE AggregateFuncTest1

结果:

只要看一看、运行一下就会发现,聚合函数的选择是根据需要来的。当我有2个星期一的入账时,由AVG函数的来的结果是不同的。

那么,求最大就是用Max,最小用Min,求和就是Sum,等等等等。没啥说的。

如果数据是字符类型呢?

再看例子。例如,办公室值日表。

 1 USE tempdb
 2 GO
 3 
 4 IF OBJECT_ID('AggregateFuncTest2','U') IS NOT NULL
 5 DROP TABLE AggregateFuncTest2
 6 GO
 7 
 8 CREATE TABLE AggregateFuncTest2
 9 (
10     ID INT IDENTITY(1,1) PRIMARY KEY,
11     DayOfWeek NVARCHAR(3),
12     Name NVARCHAR(10)
13 )
14 GO
15 
16 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期一','Jack')
17 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期二','Lucy')
18 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期三','Lily')
19 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期四','Bob')
20 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期五','Angela')
21 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期六','Bella')
22 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期日','')
23 GO
24 ------------------------------------------------------------------------
25 SELECT DayOfWeek,Name FROM AggregateFuncTest2
26 GO
27 
28 WITH Rst AS
29 (SELECT DayOfWeek,Name FROM AggregateFuncTest2)
30 SELECT * FROM Rst PIVOT (MAX(Name) FOR DayOfWeek IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]) ) AS PVT
31 ------------------------------------------------------------------------
32 DROP TABLE AggregateFuncTest2

结果:

单个的数据可以用Max等函数来凑合使用,因为这些函数可以接受字符类型的参数。

但如果数据多了,不止一个的时候呢?例如,又来了一个同事,于是周六多了一个人,那就顺便大扫除吧。

1 INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期六','Alice')
2 GO
3 WITH Rst AS
4 (SELECT DayOfWeek,Name FROM AggregateFuncTest2)
5 SELECT * FROM Rst PIVOT (MAX(Name) FOR DayOfWeek IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]) ) AS PVT
6 GO

结果:

Alice居然不用值日,这怎么行!怎能不给表现的机会?女汉子能顶一个天!

这种情况只能自己专门写一个聚合函数来拼接字符串了。(当然可以先拼接好字符串,再行转列。)

=======================================================================

用户自定义聚合函数好复杂,不写了。(其实是我还没搞懂这些呢,C#无爱。)

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL - COUNT() OVER() 函数用法详解
MySQL - COUNT() OVER() 函数用法详解
151 0
Error Code: 2006 - MySQL server has gone away
造成这个问题是因为导入数据时文件太大 解决办法: 1、Linux 系统的话找到配制文件 my.
803 0
SQL SERVER 2008中用C#定义压缩与解压缩函数
下面用C#2010定义一个函数,供SQL SERVER 2008使用: 下面是压缩函数 1 using System.Data.SqlClient; 2 using System.Data.
724 0
SQLServer2000的自定义聚合函数(转)
--和行转列的应用效果差不多了,统计结果中可能会用到--测试表create table Test(F1 varchar(10), F2 varchar(10)) go --插入数据insert into Test select 'jack' F1,'book1' F2unionselect 'jac...
788 0
SQL SERVER 2008 R2序列号
SQL SERVER 2008 R2序列号: 数据中心版:PTTFM-X467G-P7RH2-3Q6CG-4DMYB 开 发者 版:MC46H-JQR3C-2JRHY-XYRKY-QWPVM 企    业 版:R88PF-GMCFT-KM2KR-4R7GB-43K4B 标    准 版:B6...
857 0
+关注
杰克.陈
一个安静的程序猿~
10424
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载