Search code examples
mysqldatabasegroup-bygroup-concat

How can I concatenate multiple values in a single field or column? (MySQL)

I have a table in MySQL that needs to be grouped. I am retrieving sales order with multiple items ordered

My current database query looks like this: enter image description here

As you can see, sales order 255 has 2 items bought while sales order 300 has 3 items bought

Problem: I need to concatenate all product_id and their prices in line_items field or column according to their order_id

The Output that I am desiring for is this: enter image description here

This is the query that i used:

SELECT bsb.ORDER_ID AS order_id, bsb.PRODUCT_ID AS product_id, bsb.PRICE AS   product_price, '' AS line_items
FROM bsb
INNER JOIN bso ON bso.ID = bsb. ORDER_ID
WHERE bsb.ORDER_ID in(255, 300)

Kindly Please help me...

Solution

  • You can use CONCAT inside GROUP_CONCAT to have your desired result set,but keep in mind GROUP_CONCAT has a default of 1024 characters to concatenate but it can be increased by following GROUP_CONCAT manual

    SELECT bsb.ORDER_ID AS order_id, 
GROUP_CONCAT(CONCAT('product_id: ',bsb.PRODUCT_ID,' | ',bsb.PRICE) SEPARATOR ';') AS line_items
FROM bsb
INNER JOIN bso ON bso.ID = bsb. ORDER_ID
WHERE bsb.ORDER_ID in(255, 300)
GROUP BY bsb.ORDER_ID