teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

1

追記

2019/05/20 07:45

投稿

yambejp
yambejp

スコア117923

answer CHANGED
@@ -18,4 +18,57 @@
18
18
  ,sum(val*(indicator='users')) as users
19
19
  from daily_kpi
20
20
  group by dt;
21
+ ```
22
+
23
+ # 追記
24
+ indicatorを別途マスター管理し、表示順を確定すればprocedureを利用できます
25
+ - 元テーブル
26
+ ```SQL
27
+ create table daily_kpi(dt date,id int ,val int);
28
+ insert into daily_kpi values
29
+ ('2017-01-01',1,1800),
30
+ ('2017-01-01',2,500),
31
+ ('2017-01-01',3,200),
32
+ ('2017-01-02',1,2000),
33
+ ('2017-01-02',2,700),
34
+ ('2017-01-02',3,250);
35
+
36
+ create table indicator(id int primary key,name varchar(20));
37
+ insert into indicator values
38
+ (1,'impressions'),
39
+ (2,'sessions'),
40
+ (3,'users');
41
+ ```
42
+ - procedure作成
43
+ ```SQL
44
+ drop procedure if exists yokogaki;
45
+ delimiter //
46
+ create procedure yokogaki()
47
+ begin
48
+ declare a int;
49
+ declare b varchar(255);
50
+ declare done int default 0;
51
+ declare cur cursor for
52
+ select id,name from indicator order by id;
53
+ declare continue handler for sqlstate '02000' set done=1;
54
+ set @sql='select dt';
55
+ open cur;
56
+ repeat
57
+ fetch cur into a,b;
58
+ if not done then
59
+ set @sql=CONCAT(@sql,",sum(val*(id=",a,")) as ",b);
60
+ end if;
61
+ until done end repeat;
62
+ close cur;
63
+ set @sql=CONCAT(@sql," from daily_kpi group by dt order by id");
64
+ prepare stmt from @sql;
65
+ execute stmt;
66
+ end
67
+ //
68
+ delimiter ;
69
+ ```
70
+ - 実行
71
+ ```SQL
72
+
73
+ call yokogaki;
21
74
  ```