MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test_table (
value DECIMAL(9,2),
id INT,
date DATE);

INSERT INTO test_table VALUES
('64.98','123456','2021-01-01'),('64.98','123456','2021-01-02'),
('64.98','123456','2021-01-03'),('64.98','123456','2021-01-04'),
('64.98','123456','2021-01-05'),('64.98','123456','2021-01-06'),
('72.34','123456','2021-01-07'),('64.98','123456','2021-01-08'),
('64.98','123456','2021-01-09'),('103.80','123456','2021-01-10'),
('64.98','123456','2021-01-11'),('64.98','123456','2021-01-12'),
('64.98','123456','2021-01-13'),('64.98','123456','2021-01-14'),
('64.98','123456','2021-01-15'),('64.98','123456','2021-01-16'),
('64.98','123456','2021-01-17'),('64.98','123456','2021-01-18'),
('64.98','123456','2021-01-19'),('64.98','123456','2021-01-20'),
('64.98','123456','2021-01-21'),('64.98','123456','2021-01-22'),
('64.98','123456','2021-01-23'),('64.98','123456','2021-01-24'),
('144.98','123456','2021-01-25'),('64.98','123456','2021-01-26'),
('64.98','123456','2021-01-27'),('64.98','123456','2021-01-28'),
('64.98','123456','2021-01-29'),('64.98','123456','2021-01-30'),
('64.98','123456','2021-01-31'),('64.98','123456','2021-02-01'),
('64.98','123456','2021-02-02'),('64.98','123456','2021-02-03'),
('64.98','123456','2021-02-04'),('720.92','123456','2021-02-05'),
('66.98','123456','2021-02-06'),('66.98','123456','2021-02-07'),
('66.98','123456','2021-02-08'),('64.98','123456','2021-02-09'),
('64.98','123456','2021-02-10'),('64.98','123456','2021-02-11'),
('64.98','123456','2021-02-12'),('64.98','123456','2021-02-13'),
('64.98','123456','2021-02-14'),('64.98','123456','2021-02-15'),
('64.98','123456','2021-02-16'),('64.98','123456','2021-02-17'),
('64.98','123456','2021-02-18'),('64.98','123456','2021-02-19'),
('64.98','123456','2021-02-20'),('64.98','223456','2021-01-20'),
('54.98','223456','2021-01-21');
SHOW VARIABLES like 'group_concat_max_len';
SET SESSION group_concat_max_len = 1000000; 


Variable_name
Value


group_concat_max_len
1024


/*Separating the @columns and @sql variable*/

SET @columns := NULL;
SET @sql := NULL;
SET @maxrows := 20; /*change here only*/

SELECT GROUP_CONCAT(cols SEPARATOR ', \r\n') 
            INTO @columns
FROM (
SELECT CONCAT('SUM(CASE WHEN rn =',seq,' THEN vals ELSE 0 END) AS "day_',seq,'"') 
         AS cols, seq
FROM 
(SELECT s1*s2+s3 seq
FROM 
(SELECT 1 s1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ss1 CROSS JOIN
(SELECT 1 s2 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ss2 CROSS JOIN 
(SELECT 0 s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) ss3
GROUP BY seq) s WHERE seq <= @maxrows UNION
SELECT CONCAT('SUM(CASE WHEN rn >',seq,' THEN vals ELSE 0 END) AS "day_others"'), seq
FROM 
(SELECT s1*s2+s3 seq
FROM 
(SELECT 1 s1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ss1 CROSS JOIN
(SELECT 1 s2 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) ss2 CROSS JOIN 
(SELECT 0 s3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) ss3
GROUP BY seq) s 
WHERE seq=@maxrows
ORDER BY seq) c;

SELECT CONCAT('SELECT id,',@columns,'
FROM
(SELECT t1.*,
       CASE WHEN id=@idx THEN @rnk := @rnk+1 
        ELSE @rnk := 1 END AS rn,
        @idx := id
 FROM (SELECT id, 
         SUM(value) AS vals, DATE_FORMAT(date,"%Y-%m-%d") AS dt 
   FROM test_table 
   GROUP BY id, dt)t1
 CROSS JOIN (SELECT @rnk := 0, @idx := NULL) r
 ORDER BY id, dt DESC) v
GROUP BY id;') INTO @sql;

SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


@sql


SELECT id,SUM(CASE WHEN rn =1 THEN vals ELSE 0 END) AS "day_1", 
SUM(CASE WHEN rn =2 THEN vals ELSE 0 END) AS "day_2", 
SUM(CASE WHEN rn =3 THEN vals ELSE 0 END) AS "day_3", 
SUM(CASE WHEN rn =4 THEN vals ELSE 0 END) AS "day_4", 
SUM(CASE WHEN rn =5 THEN vals ELSE 0 END) AS "day_5", 
SUM(CASE WHEN rn =6 THEN vals ELSE 0 END) AS "day_6", 
SUM(CASE WHEN rn =7 THEN vals ELSE 0 END) AS "day_7", 
SUM(CASE WHEN rn =8 THEN vals ELSE 0 END) AS "day_8", 
SUM(CASE WHEN rn =9 THEN vals ELSE 0 END) AS "day_9", 
SUM(CASE WHEN rn =10 THEN vals ELSE 0 END) AS "day_10", 
SUM(CASE WHEN rn =11 THEN vals ELSE 0 END) AS "day_11", 
SUM(CASE WHEN rn =12 THEN vals ELSE 0 END) AS "day_12", 
SUM(CASE WHEN rn =13 THEN vals ELSE 0 END) AS "day_13", 
SUM(CASE WHEN rn =14 THEN vals ELSE 0 END) AS "day_14", 
SUM(CASE WHEN rn =15 THEN vals ELSE 0 END) AS "day_15", 
SUM(CASE WHEN rn =16 THEN vals ELSE 0 END) AS "day_16", 
SUM(CASE WHEN rn =17 THEN vals ELSE 0 END) AS "day_17", 
SUM(CASE WHEN rn =18 THEN vals ELSE 0 END) AS "day_18", 
SUM(CASE WHEN rn =19 THEN vals ELSE 0 END) AS "day_19", 
SUM(CASE WHEN rn =20 THEN vals ELSE 0 END) AS "day_20", 
SUM(CASE WHEN rn >20 THEN vals ELSE 0 END) AS "day_others"
FROM
(SELECT t1.*,
       CASE WHEN id=@idx THEN @rnk := @rnk+1 
        ELSE @rnk := 1 END AS rn,
        @idx := id
 FROM (SELECT id, 
         SUM(value) AS vals, DATE_FORMAT(date,"%Y-%m-%d") AS dt 
   FROM test_table 
   GROUP BY id, dt)t1
 CROSS JOIN (SELECT @rnk := 0, @idx := NULL) r
 ORDER BY id, dt DESC) v
GROUP BY id;




id
day_1
day_2
day_3
day_4
day_5
day_6
day_7
day_8
day_9
day_10
day_11
day_12
day_13
day_14
day_15
day_16
day_17
day_18
day_19
day_20
day_others


123456
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
66.98
66.98
66.98
720.92
64.98
64.98
64.98
64.98
2140.56

223456
54.98
64.98
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00


/*combining all in one single @sql variable*/

SET @columns := NULL;
SET @sql := NULL;
SET @maxrows := 20; /*change here only*/

WITH RECURSIVE cte AS (
SELECT 1 seq UNION ALL
SELECT seq+1 FROM cte WHERE seq+1 <= @maxrows)
SELECT CONCAT('SELECT id,',
         GROUP_CONCAT(cols SEPARATOR ', \r\n'),'
       FROM
       (SELECT SUM(value) AS vals, id, DATE_FORMAT(date,"%Y-%m-%d") AS dt,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
FROM test_table
GROUP BY id, date) v
GROUP BY id;') INTO @sql
FROM (
SELECT CONCAT('SUM(CASE WHEN rn =',seq,' THEN vals ELSE 0 END) AS "day_',seq,'"') 
         AS cols
FROM cte UNION
SELECT CONCAT('SUM(CASE WHEN rn >',seq,' THEN vals ELSE 0 END) AS "day_others"')
FROM cte WHERE seq=@maxrows) c;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


id
day_1
day_2
day_3
day_4
day_5
day_6
day_7
day_8
day_9
day_10
day_11
day_12
day_13
day_14
day_15
day_16
day_17
day_18
day_19
day_20
day_others


123456
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
64.98
66.98
66.98
66.98
720.92
64.98
64.98
64.98
64.98
2140.56

223456
54.98
64.98
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00


/*see the difference between using aggregation in subquery vs no subquery.*/
SELECT t1.*,
       CASE WHEN id=@idx THEN @rnk := @rnk+1 
        ELSE @rnk := 1 END AS rn,
        @idx := id
 FROM (SELECT id, 
         SUM(value) AS vals, DATE_FORMAT(date,"%Y-%m-%d") AS dt 
   FROM test_table 
   GROUP BY id, dt)t1
 CROSS JOIN (SELECT @rnk := 0, @idx := NULL) r
 ORDER BY id, dt DESC;
 
 SELECT id, 
         SUM(value) AS vals, DATE_FORMAT(date,"%Y-%m-%d") AS dt,
       CASE WHEN id=@idx THEN @rnk := @rnk+1 
        ELSE @rnk := 1 END AS rn,
        @idx := id
 FROM test_table t1
 CROSS JOIN (SELECT @rnk := 0, @idx := NULL) r
    GROUP BY id, dt
 ORDER BY id, dt DESC;


id
vals
dt
rn
@idx := id


123456
64.98
2021-02-20
1
123456

123456
64.98
2021-02-19
2
123456

123456
64.98
2021-02-18
3
123456

123456
64.98
2021-02-17
4
123456

123456
64.98
2021-02-16
5
123456

123456
64.98
2021-02-15
6
123456

123456
64.98
2021-02-14
7
123456

123456
64.98
2021-02-13
8
123456

123456
64.98
2021-02-12
9
123456

123456
64.98
2021-02-11
10
123456

123456
64.98
2021-02-10
11
123456

123456
64.98
2021-02-09
12
123456

123456
66.98
2021-02-08
13
123456

123456
66.98
2021-02-07
14
123456

123456
66.98
2021-02-06
15
123456

123456
720.92
2021-02-05
16
123456

123456
64.98
2021-02-04
17
123456

123456
64.98
2021-02-03
18
123456

123456
64.98
2021-02-02
19
123456

123456
64.98
2021-02-01
20
123456

123456
64.98
2021-01-31
21
123456

123456
64.98
2021-01-30
22
123456

123456
64.98
2021-01-29
23
123456

123456
64.98
2021-01-28
24
123456

123456
64.98
2021-01-27
25
123456

123456
64.98
2021-01-26
26
123456

123456
144.98
2021-01-25
27
123456

123456
64.98
2021-01-24
28
123456

123456
64.98
2021-01-23
29
123456

123456
64.98
2021-01-22
30
123456

123456
64.98
2021-01-21
31
123456

123456
64.98
2021-01-20
32
123456

123456
64.98
2021-01-19
33
123456

123456
64.98
2021-01-18
34
123456

123456
64.98
2021-01-17
35
123456

123456
64.98
2021-01-16
36
123456

123456
64.98
2021-01-15
37
123456

123456
64.98
2021-01-14
38
123456

123456
64.98
2021-01-13
39
123456

123456
64.98
2021-01-12
40
123456

123456
64.98
2021-01-11
41
123456

123456
103.80
2021-01-10
42
123456

123456
64.98
2021-01-09
43
123456

123456
64.98
2021-01-08
44
123456

123456
72.34
2021-01-07
45
123456

123456
64.98
2021-01-06
46
123456

123456
64.98
2021-01-05
47
123456

123456
64.98
2021-01-04
48
123456

123456
64.98
2021-01-03
49
123456

123456
64.98
2021-01-02
50
123456

123456
64.98
2021-01-01
51
123456

223456
54.98
2021-01-21
1
223456

223456
64.98
2021-01-20
2
223456




id
vals
dt
rn
@idx := id


123456
64.98
2021-02-20
51
123456

123456
64.98
2021-02-19
50
123456

123456
64.98
2021-02-18
49
123456

123456
64.98
2021-02-17
48
123456

123456
64.98
2021-02-16
47
123456

123456
64.98
2021-02-15
46
123456

123456
64.98
2021-02-14
45
123456

123456
64.98
2021-02-13
44
123456

123456
64.98
2021-02-12
43
123456

123456
64.98
2021-02-11
42
123456

123456
64.98
2021-02-10
41
123456

123456
64.98
2021-02-09
40
123456

123456
66.98
2021-02-08
39
123456

123456
66.98
2021-02-07
38
123456

123456
66.98
2021-02-06
37
123456

123456
720.92
2021-02-05
36
123456

123456
64.98
2021-02-04
35
123456

123456
64.98
2021-02-03
34
123456

123456
64.98
2021-02-02
33
123456

123456
64.98
2021-02-01
32
123456

123456
64.98
2021-01-31
31
123456

123456
64.98
2021-01-30
30
123456

123456
64.98
2021-01-29
29
123456

123456
64.98
2021-01-28
28
123456

123456
64.98
2021-01-27
27
123456

123456
64.98
2021-01-26
26
123456

123456
144.98
2021-01-25
25
123456

123456
64.98
2021-01-24
24
123456

123456
64.98
2021-01-23
23
123456

123456
64.98
2021-01-22
22
123456

123456
64.98
2021-01-21
21
123456

123456
64.98
2021-01-20
20
123456

123456
64.98
2021-01-19
19
123456

123456
64.98
2021-01-18
18
123456

123456
64.98
2021-01-17
17
123456

123456
64.98
2021-01-16
16
123456

123456
64.98
2021-01-15
15
123456

123456
64.98
2021-01-14
14
123456

123456
64.98
2021-01-13
13
123456

123456
64.98
2021-01-12
12
123456

123456
64.98
2021-01-11
11
123456

123456
103.80
2021-01-10
10
123456

123456
64.98
2021-01-09
9
123456

123456
64.98
2021-01-08
8
123456

123456
72.34
2021-01-07
7
123456

123456
64.98
2021-01-06
6
123456

123456
64.98
2021-01-05
5
123456

123456
64.98
2021-01-04
4
123456

123456
64.98
2021-01-03
3
123456

123456
64.98
2021-01-02
2
123456

123456
64.98
2021-01-01
1
123456

223456
54.98
2021-01-21
2
223456

223456
64.98
2021-01-20
1
223456

id	day_1	day_2	day_3	day_4	day_5	day_6	day_7	day_8	day_9	day_10	day_11	day_12	day_13	day_14	day_15	day_16	day_17	day_18	day_19	day_20	day_others
123456	64.98	64.98	64.98	64.98	64.98	64.98	64.98	64.98	64.98	64.98	64.98	64.98	66.98	66.98	66.98	720.92	64.98	64.98	64.98	64.98	2140.56
223456	54.98	64.98	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00

id	vals	dt	rn	@idx := id
123456	64.98	2021-02-20	1	123456
123456	64.98	2021-02-19	2	123456
123456	64.98	2021-02-18	3	123456
123456	64.98	2021-02-17	4	123456
123456	64.98	2021-02-16	5	123456
123456	64.98	2021-02-15	6	123456
123456	64.98	2021-02-14	7	123456
123456	64.98	2021-02-13	8	123456
123456	64.98	2021-02-12	9	123456
123456	64.98	2021-02-11	10	123456
123456	64.98	2021-02-10	11	123456
123456	64.98	2021-02-09	12	123456
123456	66.98	2021-02-08	13	123456
123456	66.98	2021-02-07	14	123456
123456	66.98	2021-02-06	15	123456
123456	720.92	2021-02-05	16	123456
123456	64.98	2021-02-04	17	123456
123456	64.98	2021-02-03	18	123456
123456	64.98	2021-02-02	19	123456
123456	64.98	2021-02-01	20	123456
123456	64.98	2021-01-31	21	123456
123456	64.98	2021-01-30	22	123456
123456	64.98	2021-01-29	23	123456
123456	64.98	2021-01-28	24	123456
123456	64.98	2021-01-27	25	123456
123456	64.98	2021-01-26	26	123456
123456	144.98	2021-01-25	27	123456
123456	64.98	2021-01-24	28	123456
123456	64.98	2021-01-23	29	123456
123456	64.98	2021-01-22	30	123456
123456	64.98	2021-01-21	31	123456
123456	64.98	2021-01-20	32	123456
123456	64.98	2021-01-19	33	123456
123456	64.98	2021-01-18	34	123456
123456	64.98	2021-01-17	35	123456
123456	64.98	2021-01-16	36	123456
123456	64.98	2021-01-15	37	123456
123456	64.98	2021-01-14	38	123456
123456	64.98	2021-01-13	39	123456
123456	64.98	2021-01-12	40	123456
123456	64.98	2021-01-11	41	123456
123456	103.80	2021-01-10	42	123456
123456	64.98	2021-01-09	43	123456
123456	64.98	2021-01-08	44	123456
123456	72.34	2021-01-07	45	123456
123456	64.98	2021-01-06	46	123456
123456	64.98	2021-01-05	47	123456
123456	64.98	2021-01-04	48	123456
123456	64.98	2021-01-03	49	123456
123456	64.98	2021-01-02	50	123456
123456	64.98	2021-01-01	51	123456
223456	54.98	2021-01-21	1	223456
223456	64.98	2021-01-20	2	223456