REFER below test query for your reference and try to impliment it by using your code logic. you either need to use case stament ot dynamic columns for reference check the Link MySQL pivot table query with dynamic columns
MYSQL
CREATE TEMPORARY TABLE Orders(orderId INT ,CustomerName Varchar(20));
CREATE TEMPORARY TABLE OrderDetails(orderId INT,ProductName VARCHAR(20),qty INT);
INSERT INTO Orders
SELECT 1,'Customer 1'
UNION ALL
SELECT 2,'Customer 2'
UNION ALL
SELECT 3,'Customer 3'
;
INSERT INTO OrderDetails
SELECT 1,'Pen',2
UNION ALL
SELECT 1,'Pencil',3
UNION ALL
SELECT 1,'Note',1
UNION ALL
SELECT 2,'Pen',1
UNION ALL
SELECT 2,'Pencil',1
;
SELECT o.orderId
,o.CustomerName
,CASE WHEN od.ProductName = 'Pen' THEN od.qty ELSE 0 END Pen
,CASE WHEN od.ProductName = 'Pencil' THEN od.qty ELSE 0 END Pencil
,CASE WHEN od.ProductName = 'Note' THEN od.qty ELSE 0 END Note
FROM Orders o LEFT OUTER JOIN OrderDetails od
on o.orderId = od.orderId
GROUP BY o.orderId;
-- or by dynamic query
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'CASE WHEN od.ProductName = ''',
ProductName,
''' THEN od.qty ELSE 0 END ',
ProductName
)
) INTO @sql
FROM OrderDetails;
SET @sql = CONCAT('SELECT o.orderId
,o.CustomerName ', @sql, '
FROM product p
FROM Orders o LEFT OUTER JOIN OrderDetails od
on o.orderId = od.orderId
GROUP BY o.orderId');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE Orders;
DROP TABLE OrderDetails;
Output
orderId |
CustomerName |
Pen |
Pencil |
Note |
1 |
Customer 1 |
2 |
3 |
1 |
2 |
Customer 2 |
1 |
1 |
0 |
3 |
Customer 3 |
0 |
0 |
0 |