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');
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 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;


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


SHOW VARIABLES like 'group_concat_max_len';
SET SESSION group_concat_max_len = 1000000;


Variable_name
Value


group_concat_max_len
1024


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 * FROM cte


seq


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20



WITH RECURSIVE cte AS (
SELECT 1 seq UNION ALL
SELECT seq+1 FROM cte WHERE seq+1 <= @maxrows)
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
FROM cte UNION
SELECT CONCAT('SUM(CASE WHEN rn >',seq,' THEN vals ELSE 0 END) AS "day_others"')
FROM cte WHERE seq=@maxrows) c;

SELECT @columns;


@columns


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"


SELECT CONCAT('SELECT id,',@columns,'
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;

SELECT @sql;


@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 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;


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
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