清明假期大家是否都出去浪啦云稚，小編卻被張胖子逼著在家寫sql攻略（先抱怨一下）隧饼。在此小編向你發(fā)出誠摯的學(xué)習(xí)邀請。
好啦静陈，話不多說咱們進(jìn)入今天的正題啦燕雁。我們在做數(shù)據(jù)分析的時候也許碰到最多的事情就是數(shù)據(jù)整理啦诞丽。數(shù)據(jù)整理說簡單也挺麻煩的事情，那我們今天來看一個能提高我們效率的函數(shù)——pivot拐格。
咱先做數(shù)據(jù)準(zhǔn)備咱們做一個這樣的表
上代碼：
/*建表先*/
CREATE TABLE T_SCORE
(CLASS_ID VARCHAR2(20 BYTE),
STD_ID VARCHAR2(20 BYTE),
SUBJECT VARCHAR2(20 BYTE),
SCORE NUMBER);
?
建好表咱就差數(shù)據(jù)啦
1/插入數(shù)據(jù)先/
2INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’語文’,’97’);
3INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1231’,’01’,’數(shù)學(xué)’,’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’,’化學(xué)’,’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’,’數(shù)學(xué)’,’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’,’化學(xué)’,’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’,’數(shù)學(xué)’,’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’,’化學(xué)’,’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’,’數(shù)學(xué)’,’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’,’化學(xué)’,’74’);
25INSERTINTOT_SCORE (CLASS_ID, STD_ID, SUBJECT, SCORE)VALUES(‘A1232’,’02’,’物理’,’81’);
26commit;/提交數(shù)據(jù)/
?
????好啦這樣我們的數(shù)據(jù)準(zhǔn)備就做好了僧免，我要開始我的表演啦。
1selectCLASS_ID,STD_ID,語文,數(shù)學(xué),英語,生物,化學(xué),物理
2fromt_scorepivot(sum(SCORE)forSUBJECT
3in('語文'as語文,'數(shù)學(xué)'as數(shù)學(xué),'英語'as英語,'生物'as生物,'化學(xué)'as化學(xué),'物理'as物理));
?
以上就是這段代碼跑完的結(jié)果啦捏浊，我們可以發(fā)現(xiàn)我們將原來的科目字段轉(zhuǎn)置了一下啦懂衩。短短一段代碼實現(xiàn)就實現(xiàn)了我的要求，從此這種問題就可以告別繁瑣的case when 啦金踪。
提一下pivot 要注意的一些問題浊洞，就用剛剛的代碼來舉例pivot(sum(SCORE) for SUBJECT in (...)) 。
1胡岔、for前面的函數(shù)必須是聚合函數(shù)法希。
2、for前面可以寫多個函數(shù)做多個字段（代碼附上靶瘸，小伙伴們可以自己試試哦）苫亦。
1select*
2fromt_scorepivot(sum(SCORE)as分?jǐn)?shù),count(STD_ID) 人數(shù)forSUBJECTin
3('語文'as語文,'數(shù)學(xué)'as數(shù)學(xué),'英語'as英語,'生物'as生物,'化學(xué)'as化學(xué),'物理'as物理));
?
sum(SCORE) 分?jǐn)?shù),count(STD_ID) 人數(shù)這里的重命名是相當(dāng)?shù)沃匾呐丁?/p>
好啦，今天的內(nèi)容就這樣啦怨咪，期待我下次給你們帶來點啥可以留言告訴我哈屋剑。
