SQL練習：Pivot

PTT DataBase版 JOIN問題#1J_6stCR

有以下兩張TABLE，其中ABC欄都是string，A and B and C是key值，D E欄用來判斷number2的屬性

題目：呈現以下內容

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

--創建測試資料表 CREATE TABLE Table_1 ( A varchar(10) NULL, B varchar(10) NULL, C varchar(10) NULL, number1 int NULL ) ON [PRIMARY] insert into Table_1(A,B,C,number1) values ('甲','乙','丙',10) insert into Table_1(A,B,C,number1) values ('甲','乙','丁',20) insert into Table_1(A,B,C,number1) values ('乙','丙','丁',30) CREATE TABLE Table_2 ( A varchar(10) NULL, B varchar(10) NULL, C varchar(10) NULL, D varchar(10) NULL, E varchar(10) NULL, number2 int NULL ) ON [PRIMARY] insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丙','T','F',1) insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丙','T','T',2) insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丙','F','F',3) insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丁','T','F',4) insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丁','T','T',5) insert into Table_2(A,B,C,D,E,number2) values ('甲','乙','丁','F','F',6) insert into Table_2(A,B,C,D,E,number2) values ('乙','丙','丁','T','F',7) insert into Table_2(A,B,C,D,E,number2) values ('乙','丙','丁','T','T',8) insert into Table_2(A,B,C,D,E,number2) values ('乙','丙','丁','F','F',9)

解法

將Table_1併入Table_2，KEY A,B,C，產出的表將欄位D,E字串組合

使用樞紐指令pivot，將組合後的字串F和number2進行樞紐表格 為PV

1 2 3 4 5 6 7 8 9 10 11 12 13

select * from(select Table_3.A, Table_3.B, Table_3.C, Table_3.D+Table_3.E as F, --將D、E欄位組合成字串設為F Table_3.number2, Table_1.number1 from Table_1 right outer join Table_2 as Table_3 --將Table_1併入Table_2，KEY A,B,C on Table_1.A=Table_3.A AND Table_1.B=Table_3.B AND Table_1.C=Table_3.C) AS Table_4--樞紐來源資料 --使用樞紐指令pivot，將組合後的字串F和number2進行樞紐表格 PV pivot ( sum (number2) for F IN ([TF],[TT],[FF])) as PV--樞紐欄位 order by number1