CODEWITHSUNDEEP
phpmysqli
Yash M. Hanj
Yash M. Hanj

Reputation: 495

How to join two tables of mysql and concatenate multiple rows into single cell?

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

CustomerName          OrderId

John Doe                                     101

John Doe                                     102

John Doe                                     103

John Doe                                     104

Expected Output::

CustomerName          OrderId

John Doe                            101,102,103,104

Upvotes: 6

Views: 1028

Answers (3)

Hercules
Hercules

Reputation: 26

Group concat is the easiest way to achieve the output you require.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521609

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.

Demo

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

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;

Upvotes: 0

Related Questions