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 |