MySQL 5.5

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

CREATE TABLE Orders( id INT, name VARCHAR(50), amount INT, date DATE);
CREATE TABLE Order_details( id INT, Item VARCHAR(1), Qty INT);
INSERT INTO Orders VALUES
(1,'ABC','100','2020-10-01'),
(2,'XYZ','200','2020-10-01'),
(3,'MNO','250','2020-11-01')
INSERT INTO Order_details VALUES
(1,'A',2),
(1,'B',1),
(1,'C',3),
(2,'X',1),
(3,'A',4),
(1,'D',4),
(1,'E',5),
(1,'F',6),
(1,'G',7),
(1,'H',8),
(1,'I',9),
(1,'J',10),
(1,'K',11),
(1,'L',12)
SET SESSION group_concat_max_len = 18446744073709551615;
SET @sql = NULL;
SET @date = '2020-10-01';

SELECT GROUP_CONCAT(
           DISTINCT
             CONCAT(
                    'MAX(CASE WHEN rn = ', rn,' THEN Item END ) AS Item', rn,
                    ', MAX(CASE WHEN rn = ', rn,' THEN Qty END ) AS Qty'
                    )
       )
  INTO @sql
  FROM ( 
        SELECT *, @rn := IF(@i = id, @rn + 1, 1) AS rn, @i := id
          FROM Order_details
          JOIN (SELECT @i := 0, @rn := 0) i
         ORDER BY id, Item
  ) od;

SET @sql = CONCAT('SELECT o.id, o.name, o.amount, o.date,',@sql,
                   ' FROM Orders o
                     JOIN (
                           SELECT *, @rn := IF(@i = id, @rn + 1, 1) AS rn, @i := id
                             FROM Order_details
                             JOIN (SELECT @i := 0, @rn := 0) i
                            ORDER BY id, Item    
                          ) od
                       ON od.id = o.id
                    WHERE o.date = "',@date,'"
                    GROUP BY o.id, o.name, o.amount, o.date
                    ORDER BY o.id');

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


id
name
amount
date
Item1
Qty
Item2
Qty
Item3
Qty
Item4
Qty
Item5
Qty
Item6
Qty
Item7
Qty
Item8
Qty
Item9
Qty
Item10
Qty
Item11
Qty
Item12
Qty


1
ABC
100
2020-10-01
A
2
B
1
C
3
D
4
E
5
F
6
G
7
H
8
I
9
J
10
K
11
L
12

2
XYZ
200
2020-10-01
X
1
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null

id	name	amount	date	Item1	Qty	Item2	Qty	Item3	Qty	Item4	Qty	Item5	Qty	Item6	Qty	Item7	Qty	Item8	Qty	Item9	Qty	Item10	Qty	Item11	Qty	Item12	Qty
1	ABC	100	2020-10-01	A	2	B	1	C	3	D	4	E	5	F	6	G	7	H	8	I	9	J	10	K	11	L	12
2	XYZ	200	2020-10-01	X	1	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null