· 8 years ago · Aug 30, 2018, 03:50 AM
1MySQL pivot table with dynamic headers based on single column data
2SELECT cs.`category_id`, cs.`ProcessDate`, cs.`PercentChange`
3 FROM `Category_Statistics` cs
4 WHERE cs.`ProcessDate` >= '2011-05-10'
5 AND cs.`ProcessDate` <= '2011-05-14'
6
7CategoryId | ProcessDate | PercentChange
8-------------------------------------------
9category_4 | 2011-05-10 | 10
10category_4 | 2011-05-11 | 18
11category_4 | 2011-05012 | 12
12...
13category_7 | 2011-05-10 | 21
14category_7 | 2011-05-11 | 7
15...
16category_12 | 2011-05-10 | 7
17category_12 | 2011-05-11 | 15
18
19CategoryId | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 | 2011-05-14 |
20--------------------------------------------------------------------------------
21category_4 | 10 | 18 | 12 | 9 | 14 |
22category_7 | 21 | 7 | 16 | 14 | 13 |
23categeory_12 | 7 | 15 | 11 | 19 | 8 |
24--------------------------------------------------------------------------------
25
26SELECT `CategoryId`,
27 MAX(IF(c.`ProcessedOn` = '2011-04-20', c.`PercentChange`, NULL)) AS '2011-04-20',
28 MAX(IF(c.`ProcessedOn` = '2011-04-21', c.`PercentChange`, NULL)) AS '2011-04-21',
29 MAX(IF(c.`ProcessedOn` = '2011-04-22', c.`PercentChange`, NULL)) AS '2011-04-22',
30 MAX(IF(c.`ProcessedOn` = '2011-04-23', c.`PercentChange`, NULL)) AS '2011-04-23',
31 MAX(IF(c.`ProcessedOn` = '2011-04-24', c.`PercentChange`, NULL)) AS '2011-04-24'
32 FROM `Category_Gravity` c
33 WHERE c.`ProcessedOn` >= '2011-04-20'
34 AND c.`ProcessedOn` <= '2011-04-24'
35 GROUP BY `CategoryId`
36
37MAX(IF(c.`ProcessedOn` = '2011-04-20', c.`PercentChange`, NULL)) AS '2011-04-20',
38
39create table `pivot` (
40 `id` int(11) not null auto_increment,
41 `categoryid` int(11) default null,
42 `processdate` date default null,
43 `percentchange` int(11) default null,
44 primary key (`id`)
45) engine=myisam auto_increment=9 default charset=latin1;
46
47/*Data for the table `pivot` */
48
49insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (1,4,'2011-05-10',1);
50insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (2,4,'2011-05-11',22);
51insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (3,4,'2011-05-12',3);
52insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (4,7,'2011-05-10',4);
53insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (5,7,'2011-05-11',5);
54insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (6,12,'2011-05-10',6);
55insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (7,12,'2011-05-12',7);
56insert into `pivot`(`id`,`categoryid`,`processdate`,`percentchange`) values (8,4,'2011-05-13',12);
57
58
59
60delimiter //
61drop procedure if exists dynamic_view2//
62create procedure dynamic_view2(in sdate date,in edate date)
63begin
64declare finish int default 0;
65declare cdate date;
66declare str varchar(10000) default "select categoryid,";
67declare curs cursor for select processdate from pivot where processdate between sdate and edate group by processdate;
68declare continue handler for not found set finish = 1;
69open curs;
70my_loop:loop
71fetch curs into cdate;
72if finish = 1 then
73leave my_loop;
74end if;
75set str = concat(str, "max(case when processdate = '",cdate,"' then percentchange else null end) as `",cdate,"`,");
76end loop;
77close curs;
78set str = substr(str,1,char_length(str)-1);
79set @str = concat(str," from pivot
80 group by categoryid");
81
82prepare stmt from @str;
83execute stmt;
84deallocate prepare stmt;
85end;//
86delimiter ;
87
88
89mysql> call dynamic_view2('2011-05-10','2011-05-13');
90+------------+------------+------------+------------+------------+
91| categoryid | 2011-05-10 | 2011-05-11 | 2011-05-12 | 2011-05-13 |
92+------------+------------+------------+------------+------------+
93| 4 | 1 | 22 | 3 | 12 |
94| 7 | 4 | 5 | NULL | NULL |
95| 12 | 6 | NULL | 7 | NULL |
96+------------+------------+------------+------------+------------+
973 rows in set (0.00 sec)