sql疑难杂症——列转行pviot

清明假期大家是否都出去浪啦，小编却被张胖子逼着在家写sql攻略（先抱怨一下）。在此小编向你发出诚挚的学习邀请。

好啦，话不多说咱们进入今天的正题啦。我们在做数据分析的时候也许碰到最多的事情就是数据整理啦。数据整理说简单也挺麻烦的事情，那我们今天来看一个能提高我们效率的函数——pivot。

咱先做数据准备咱们做一个这样的表

上代码：

/*建表先*/
CREATE TABLE T_SCORE
(CLASS_ID VARCHAR2(20 BYTE), 
STD_ID VARCHAR2(20 BYTE), 
SUBJECT VARCHAR2(20 BYTE),
SCORE NUMBER);

​

建好表咱就差数据啦

1/插入数据先/
2INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’语文’,’97’);
3INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’数学’,’120’);
4INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’英语’,’107’);
5INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’生物’,’86’);
6INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’化学’,’92’);
7INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’物理’,’88’);
8INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’语文’,’106’);
9INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’数学’,’98’);
10INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’英语’,’121’);
11INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’生物’,’79’);
12INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’化学’,’68’);
13INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’02’,’物理’,’66’);
14INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’语文’,’95’);
15INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’数学’,’110’);
16INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’英语’,’87’);
17INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’生物’,’88’);
18INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’化学’,’95’);
19INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’01’,’物理’,’98’);
20INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’语文’,’107’);
21INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’数学’,’100’);
22INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’英语’,’80’);
23INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’生物’,’81’);
24INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’化学’,’74’);
25INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’物理’,’81’);
26commit;/提交数据/

​

    好啦这样我们的数据准备就做好了，我要开始我的表演啦。

1selectCLASS_ID,STD_ID,语文,数学,英语,生物,化学,物理
2fromt_scorepivot(sum(SCORE)forSUBJECT
3in('语文'as语文,'数学'as数学,'英语'as英语,'生物'as生物,'化学'as化学,'物理'as物理));

​

以上就是这段代码跑完的结果啦，我们可以发现我们将原来的科目字段转置了一下啦。短短一段代码实现就实现了我的要求，从此这种问题就可以告别繁琐的case when 啦。

提一下pivot 要注意的一些问题，就用刚刚的代码来举例pivot(sum(SCORE) for SUBJECT in (...)) 。

1、for前面的函数必须是聚合函数。

2、for前面可以写多个函数做多个字段（代码附上，小伙伴们可以自己试试哦）。

1select*
2fromt_scorepivot(sum(SCORE)as分数,count(STD_ID) 人数forSUBJECTin
3('语文'as语文,'数学'as数学,'英语'as英语,'生物'as生物,'化学'as化学,'物理'as物理));

​

sum(SCORE) 分数,count(STD_ID) 人数这里的重命名是相当滴重要的哦。

好啦，今天的内容就这样啦，期待我下次给你们带来点啥可以留言告诉我哈。