How to join two tables of sql and concatenate multiple rows into single cell?
The Query which i am using::
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;
Output which i got::
John Doe 101
John Doe 102
John Doe 103
John Doe 104
Expected Output::
John Doe 101,102,103,104
Group concat is the easiest way to achieve the output you require.
Use GROUP_CONCAT and aggregate by customer to generate a CSV list of orders:
SELECT
c.CustomerName,
GROUP_CONCAT(o.OrderID) AS OrderIDs
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerId;
Note that it would be preferable to aggregate by customer ID, if possible, because perhaps two or more customers have the same name.
Use group_concat
SELECT Customers.CustomerName, group_concat(Orders.OrderID) as OrderID
FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID
group by Customers.CustomerName;
