How can I concatenate multiple values in one field or column? (Database)
I have a table in MySQL that needs to be grouped. I am fetching a sales order with multiple items ordered
My current database query looks like this:
As you can see, there are 2 items sold in sales order 255, while purchase order 300 has 3 items purchased
Problem: I need to concatenate all product_id and their prices in a line_items field or column according to their order_id
The result I want is this:
This is the query 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)
Please help me...
source to share
You can use CONCAT
internally GROUP_CONCAT
to have the result set you want, but keep in mind that there GROUP_CONCAT
is a default of 1024 characters for concatenation, but it can be increased by following GROUP_CONCAT
the user guide
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
source to share