How to join multiple fields in order using mySQL query?

Question

I have a table where it has Size and Price for each product. There could be multiple sizes with prices. I want to read size and price with ":" separated while each size:price is separated by commas for each product.

If I read it using SQL query I get the following.

PRODUCT_ID     SIZE     PRICE
001            L        20 
001            S        15
002            M        10
002            L        20
002            S        5

I want to read the following as follows :

PRODUCT        SIZE_PRICE
001            L:20,S:15
002            M:10,L:20,S:5

What is the best way to do this ?

Why did you just delete your other question?

Enigmativity
– Enigmativity

2019-03-24 05:31:17 +00:00
Commented Mar 24, 2019 at 5:31 — Enigmativity
– Enigmativity, Commented Mar 24, 2019 at 5:31

D-Shih · Accepted Answer · 2019-03-24 04:24:45Z

2

You can try to use group_concat with CONCAT

Schema (MySQL v5.7)

CREATE TABLE T(
   PRODUCT_ID varchar(50),
   SIZE varchar(50),
   PRICE int
);


INSERT INTO T VALUES ('001','L',20); 
INSERT INTO T VALUES ('001','S',15);
INSERT INTO T VALUES ('002','M',10);
INSERT INTO T VALUES ('002','L',20);
INSERT INTO T VALUES ('002','S',5);

Query #1

SELECT PRODUCT_ID,
      GROUP_CONCAT(CONCAT(SIZE,':',PRICE)) 'SIZE_PRICE'
FROM T
GROUP BY PRODUCT_ID;

| PRODUCT_ID | SIZE_PRICE    |
| ---------- | ------------- |
| 001        | L:20,S:15     |
| 002        | M:10,L:20,S:5 |

View on DB Fiddle

answered Mar 24, 2019 at 4:24

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

. . You don't need CONCAT(). Just use GROUP_CONCAT( SIZE, ':', PRICE). GROUP_CONCAT() takes multiple arguments.

Collectives™ on Stack Overflow

How to join multiple fields in order using mySQL query?

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