SQLServer2008R2——PIVOT⾏转列以及聚合函数的选择==================================声明==================================
本⽂原创，转载在正⽂中显要的注明作者和出处，并保证⽂章的完整性。
未经作者同意请勿修改（包括本声明），保留法律追究的权利。
未经作者同意请勿⽤于学术性引⽤。
未经作者同意请勿⽤于商业出版、商业印刷、商业引⽤。
本⽂不定期修正完善，为保证内容正确，建议移步原⽂处阅读。
本⽂链接：
=======================================================================
看了好长时间的资料，总算是看懂了点。（另外怎样才能把右⾯的这个长⽑的⼟⾖去掉？---->                        对，就是它！）
赶紧记录下来。
=======================================================================
表格和数据。（和wwwblogs/wlsandwho/p/4416222.html的相同）
1USE tempdb
2GO
3--------------------------------------------------------------------------
4IF OBJECT_ID('Student','U') IS NOT NULL
5DROP TABLE Student
6GO
7
8CREATE TABLE Student
9 (
10    StuID NVARCHAR(8) PRIMARY KEY,
11    Name NVARCHAR(5),
12    EntranceTime DATETIME
13 )
14GO
15
16INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20080001','Lily','2008-08-27')
17INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20090002','Lucy','2009-08-26')
18INSERT INTO Student(StuID,Name,EntranceTime) VALUES('20070003','Jack','2007-08-28')
19GO
20--------------------------------------------------------------------------
21IF OBJECT_ID('SltCourse','U') IS NOT NULL
22DROP TABLE SltCourse
23GO
24
25CREATE TABLE SltCourse    --SelectiveCourse
26 (
27    ID INT PRIMARY KEY IDENTITY(1,1),
28    StuID NVARCHAR(8),
29    CourseName NVARCHAR(10),
30    Score    INT
31 )
32GO
33
34INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','电脑维修',90)
35INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','剪纸',80)
36INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','市场策划',95)
37INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20070003','信息检索',100)
38
39INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','插花',99)
40INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','剪纸',96)
41INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20080001','刺绣',92)
42
43INSERT INTO SltCourse(StuID,CourseName,Score) VALUES('20090002','插花',98)
44GO
查看⼀下
1SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
2GO
=======================================================================
现在来看这个表格，前三列有很多数据是重复的，⽽且也不便于阅读。之前使⽤了row_number函数，这⾥使⽤下别的——PIVOT来实现⼀个⾏转列。
预期效果。
=======================================================================
静态版。这需要预知要转换成列名的数据。
1WITH ReportCard
2AS(
3SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
4 )
5SELECT*FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN([电脑维修],[剪纸],[市场策划],[信息检索],[插花],[刺绣]) ) AS T
6GO
结果。
当在第三⾏加上筛选语句时，可以得到指定的某⼀⾏。例如：
1WITH ReportCard
2AS(
3SELECT 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 )
5SELECT*FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN([电脑维修],[剪纸],[市场策划],[信息检索],[插花],[刺绣]) ) AS T
6GO
结果。
=======================================================================
动态版。先查询出要转为列的⾏数据，然后设置格式，再拼接字符串。
1DECLARE@strCN NVARCHAR(100)
2SELECT@strCN=ISNULL(@strCN+',','')+QUOTENAME(CourseName) FROM  SltCourse GROUP BY CourseName
3--PRINT @DCN
4
5DECLARE@SqlStr NVARCHAR(1000)
6SET@SqlStr='
sql server拼接字符串函数7WITH ReportCard
8AS(
9SELECT s.StuID,s.Name,s.EntranceTime,sc.CourseName,sc.Score FROM Student s,SltCourse sc WHERE s.stuid=sc.stuid
10)
11SELECT * FROM ReportCard PIVOT(MAX(Score) FOR CourseName IN('+@strCN+') ) AS T'
12EXEC(@sqlstr)
13GO
=======================================================================
题外话，关于PIVOT中聚合函数的使⽤。
我刚⽤TSQL不久，⾏转列都是汇总⼀些数值类型的数据，⽐⽅说，周⼀到周⽇的营业收⼊啦，数理化史地⽣成绩啦之类的。
这些数值类型的，选择聚合函数时，当然是需要什么选什么啦。
看例⼦。
1USE tempdb
2GO
3
4IF OBJECT_ID('AggregateFuncTest1','U') IS NOT NULL
5DROP TABLE AggregateFuncTest1
6GO
7
8CREATE TABLE AggregateFuncTest1
9 (
10    ID INT IDENTITY(1,1) PRIMARY KEY,
11    DayOfWeek NVARCHAR(3),
12    Income INT
13 )
14GO
15
16INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期⼀',100)
17INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期⼆',200)
18INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期三',300)
19INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期四',400)
20INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期五',500)
21INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期六',600)
22INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期⽇',700)
23GO
24----------------------------------------------------------------------
25SELECT DayOfWeek,Income FROM AggregateFuncTest1
26GO
27----------------------------------------------------------------------
28WITH Rst AS
29 (
30SELECT DayOfWeek,Income FROM AggregateFuncTest1
31 )
32SELECT[星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇]FROM Rst
33 PIVOT(AVG(Income) FOR DayOfWeek IN ([星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇])) AS T
34GO
35----------------------------------------------------------------------
36INSERT INTO AggregateFuncTest1(DayOfWeek,Income) VALUES('星期⼀',1000)
37GO
38
39WITH Rst AS
40 (
41SELECT DayOfWeek,Income FROM AggregateFuncTest1
42 )
43SELECT[星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇]FROM Rst
44 PIVOT(AVG(Income) FOR DayOfWeek IN ([星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇])) AS T
45GO
46----------------------------------------------------------------------
47DROP TABLE AggregateFuncTest1
结果：
只要看⼀看、运⾏⼀下就会发现，聚合函数的选择是根据需要来的。当我有2个星期⼀的⼊账时，由AVG函数的来的结果是不同的。那么，求最⼤就是⽤Max，最⼩⽤Min，求和就是Sum，等等等等。没
啥说的。
如果数据是字符类型呢？
再看例⼦。例如，办公室值⽇表。
1USE tempdb
2GO
3
4IF OBJECT_ID('AggregateFuncTest2','U') IS NOT NULL
5DROP TABLE AggregateFuncTest2
6GO
7
8CREATE TABLE AggregateFuncTest2
9 (
10    ID INT IDENTITY(1,1) PRIMARY KEY,
11    DayOfWeek NVARCHAR(3),
12    Name NVARCHAR(10)
13 )
14GO
15
16INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期⼀','Jack')
17INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期⼆','Lucy')
18INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期三','Lily')
19INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期四','Bob')
20INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期五','Angela')
21INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期六','Bella')
22INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期⽇','')
23GO
24------------------------------------------------------------------------
25SELECT DayOfWeek,Name FROM AggregateFuncTest2
26GO
27
28WITH Rst AS
29 (SELECT DayOfWeek,Name FROM AggregateFuncTest2)
30SELECT*FROM Rst PIVOT (MAX(Name) FOR DayOfWeek IN ([星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇]) ) AS PVT
31------------------------------------------------------------------------
32DROP TABLE AggregateFuncTest2
结果：
单个的数据可以⽤Max等函数来凑合使⽤，因为这些函数可以接受字符类型的参数。
但如果数据多了，不⽌⼀个的时候呢？例如，⼜来了⼀个同事，于是周六多了⼀个⼈，那就顺便⼤扫除吧。
1INSERT INTO AggregateFuncTest2(DayOfWeek,Name) VALUES('星期六','Alice')
2GO
3WITH Rst AS
4 (SELECT DayOfWeek,Name FROM AggregateFuncTest2)
5SELECT*FROM Rst PIVOT (MAX(Name) FOR DayOfWeek IN ([星期⼀],[星期⼆],[星期三],[星期四],[星期五],[星期六],[星期⽇]) ) AS PVT 6GO
结果：
Alice居然不⽤值⽇，这怎么⾏！怎能不给表现的机会？⼥汉⼦能顶⼀个天!
这种情况只能⾃⼰专门写⼀个聚合函数来拼接字符串了。（当然可以先拼接好字符串，再⾏转列。）
=======================================================================
⽤户⾃定义聚合函数好复杂，不写了。（其实是我还没搞懂这些呢，C#⽆爱。）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。