SQL練習：Pivot (2)

PTT DataBase版 JOIN問題#1IYlkmn

今天有一張資料表是長這樣的

國家  年度  月份  日期  台幣  價格  買入  賣出

國家這一欄可能會有很多國家的資料,例如大陸美國英國加拿大

最後要的資料表是長這樣

年月  美國  英國 大陸  加拿大  .....

一次查詢就是找出某一年月各個國家的平均價格，然後把它組合起來畫統計圖

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 33 34 35 36 37 38 39 40 41 42 43 44

--創建測試資料表 CREATE TABLE AA ( COUNTRY varchar(10) NULL, DATE date NULL, Price money NULL, ) ON [PRIMARY] insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-26',30.55) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-25',30.56) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-6-24',30.54) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-28',30.44) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-27',30.45) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-5-26',30.55) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-27',30.44) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-26',30.98) insert into AA(COUNTRY,DATE,Price) values ('美國','2015-4-25',30.71) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-26',47.63) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-25',47.68) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-6-24',47.68) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-28',47.61) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-27',47.68) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-5-26',47.64) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-27',47.65) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-26',47.60) insert into AA(COUNTRY,DATE,Price) values ('英國','2015-4-25',47.65) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-26',24.76) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-25',24.78) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-6-24',24.79) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-28',24.70) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-27',24.75) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-5-26',24.76) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-27',24.75) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-26',24.70) insert into AA(COUNTRY,DATE,Price) values ('加拿大','2015-4-25',24.76) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-26',22.45) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-25',22.41) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-6-24',22.42) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-28',22.43) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-27',22.48) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-5-26',22.47) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-27',22.43) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-26',22.41) insert into AA(COUNTRY,DATE,Price) values ('新加坡','2015-4-25',22.43)

解法

先取出年月進行Group，再使用pivot進行樞紐

1 2 3 4 5 6 7 8 9 10

select * From( select CONVERT(varchar,DATEPART(yy,DATE))+REPLICATE('0',2-LEN(CONVERT(varchar,Datepart(mm,DATE))))+CONVERT(varchar,DATEPART(mm,DATE)) as 年月, COUNTRY, Price from AA Group by CONVERT(varchar,DATEPART(yy,DATE))+REPLICATE('0',2-LEN(CONVERT(varchar,Datepart(mm,DATE))))+CONVERT(varchar,DATEPART(mm,DATE)), COUNTRY,Price) AS PRV --取得年月進行Group pivot ( AVG (Price) FOR COUNTRY IN ([美國],[英國],[加拿大],[新加坡])) AS PV

顯示結果