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

Question

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

M Khalid Junaid · Accepted Answer · 2014-08-20 07:09:28Z

1

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

answered Aug 20, 2014 at 7:09

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nobunaga Over a year ago

Hi Khalid, Thanks for your response. Your answer is correct. You are awesome! :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related