teratail
回答率
85.47%
質問するログイン新規登録
トップSQLに関する質問SQLで縦横を変換したい

編集履歴

回答編集履歴

3

typo

2017/07/20 01:24

投稿

yambejp
yambejp

スコア114883

test CHANGED
@@ -50,7 +50,7 @@
50
50
 
51
51
 
52
52
 
53
- ``
53
+ ```
54
54
 
55
55
 
56
56
 
@@ -81,6 +81,8 @@
81
81
 
82
82
 
83
83
  - 上記をもとに集計
84
+
85
+
84
86
 
85
87
  ```SQL
86
88
 

2

修正

2017/07/20 01:24

投稿

yambejp
yambejp

スコア114883

test CHANGED
@@ -18,6 +18,8 @@
18
18
 
19
19
  ```SQL
20
20
 
21
+ /* 元データ */
22
+
21
23
  create table tbl(term varchar(10),team varchar(10),sales int, budget int,ratio double);
22
24
 
23
25
  insert into tbl values
@@ -30,25 +32,25 @@
30
32
 
31
33
  ('4Q','teamA',100,200,0.5);
32
34
 
35
+
36
+
33
- ```
37
+ /* pivot作成 */
38
+
39
+ create table pivot(term varchar(10),1Q int,2Q int,3Q int,4Q int);
40
+
41
+ insert into pivot select term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl;
34
42
 
35
43
 
36
44
 
37
- - pivot作成
45
+ /* 横方向のデータ保持 */
38
46
 
39
- ```SQL
47
+ create table cols(col varchar(10));
40
48
 
41
- create table pivot(col varchar(10),term varchar(10),1Q int,2Q int,3Q int,4Q int);
49
+ insert into cols values('sales'),('budget'),('ratio');
42
50
 
43
- insert into pivot
44
51
 
45
- select 'sales',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
46
52
 
47
- union all select 'budget',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
48
-
49
- union all select 'ratio',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
50
-
51
- ```
53
+ ``
52
54
 
53
55
 
54
56
 
@@ -70,13 +72,15 @@
70
72
 
71
73
  end as val
72
74
 
75
+ from (select * from pivot,cols) as t1 inner join
76
+
73
- from pivot as t1 inner join tbl as t2 on t1.term =t2.term
77
+ tbl as t2 on t1.term =t2.term ;
74
78
 
75
79
  ```
76
80
 
77
81
 
78
82
 
79
- - 集計
83
+ - 上記をもとに集計
80
84
 
81
85
  ```SQL
82
86
 
@@ -106,11 +110,13 @@
106
110
 
107
111
  end as val
108
112
 
113
+ from (select * from pivot,cols) as t1 inner join
114
+
109
- from pivot as t1 inner join tbl as t2 on t1.term =t2.term
115
+ tbl as t2 on t1.term =t2.term
110
116
 
111
117
  ) sub
112
118
 
113
- group by col
119
+ group by col;
114
120
 
115
121
 
116
122
 

1

sample

2017/07/20 01:23

投稿

yambejp
yambejp

スコア114883

test CHANGED
@@ -7,3 +7,117 @@
7
7
 
8
8
 
9
9
  また命題のデータはteamが縦横変換されておらずピボットになっていません。
10
+
11
+
12
+
13
+ # pivotを使ったsample
14
+
15
+
16
+
17
+ - テーブル作成
18
+
19
+ ```SQL
20
+
21
+ create table tbl(term varchar(10),team varchar(10),sales int, budget int,ratio double);
22
+
23
+ insert into tbl values
24
+
25
+ ('1Q','teamA',100,200,0.5),
26
+
27
+ ('2Q','teamA',100,300,0.3333),
28
+
29
+ ('3Q','teamA',100,300,0.3333),
30
+
31
+ ('4Q','teamA',100,200,0.5);
32
+
33
+ ```
34
+
35
+
36
+
37
+ - pivot作成
38
+
39
+ ```SQL
40
+
41
+ create table pivot(col varchar(10),term varchar(10),1Q int,2Q int,3Q int,4Q int);
42
+
43
+ insert into pivot
44
+
45
+ select 'sales',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
46
+
47
+ union all select 'budget',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
48
+
49
+ union all select 'ratio',term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl
50
+
51
+ ```
52
+
53
+
54
+
55
+ - とりあえず結合
56
+
57
+ ```SQL
58
+
59
+ select
60
+
61
+ t1.*,
62
+
63
+ case t1.col
64
+
65
+ when 'sales' then t2.sales
66
+
67
+ when 'budget' then t2.budget
68
+
69
+ when 'ratio' then t2.ratio
70
+
71
+ end as val
72
+
73
+ from pivot as t1 inner join tbl as t2 on t1.term =t2.term
74
+
75
+ ```
76
+
77
+
78
+
79
+ - 集計
80
+
81
+ ```SQL
82
+
83
+ select col
84
+
85
+ ,sum(1Q*val) as 1Q
86
+
87
+ ,sum(2Q*val) as 2Q
88
+
89
+ ,sum(3Q*val) as 3Q
90
+
91
+ ,sum(4Q*val) as 4Q
92
+
93
+ from(
94
+
95
+ select
96
+
97
+ t1.*,
98
+
99
+ case t1.col
100
+
101
+ when 'sales' then t2.sales
102
+
103
+ when 'budget' then t2.budget
104
+
105
+ when 'ratio' then t2.ratio
106
+
107
+ end as val
108
+
109
+ from pivot as t1 inner join tbl as t2 on t1.term =t2.term
110
+
111
+ ) sub
112
+
113
+ group by col
114
+
115
+
116
+
117
+ ```
118
+
119
+
120
+
121
+ ※前回も下記来ましたが結果としてteamを縦に書くのはロジックとしておかしいので
122
+
123
+ やっていません。