顯示具有 Pivot 標籤的文章。 顯示所有文章
顯示具有 Pivot 標籤的文章。 顯示所有文章

2015年6月28日 星期日

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

顯示結果


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