How do I combine separate records into one?

Question

I have a table product and two columns id,pricing_category, and I have a query

SELECT DISTINCT ppp.`id` AS productid ,ppp.`pricing_category` AS type1
FROM `product` ppp
WHERE ppp.`finish_product_id`=1

The result is:

I want that type will appear as type1,type2,type3 in a single row for one id. How should I do it?

Possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — tafa, May 30 '16 at 12:05

sagi · Accepted Answer · 2016-05-30T12:06:23.797

You can do it using conditional aggregation:

SELECT t.productID,
       MAX(CASE WHEN t.type1 = 1 THEN 1 ELSE 0 END) as type1_ind,
       MAX(CASE WHEN t.type1 = 2 THEN 1 ELSE 0 END) as type2_ind,
       MAX(CASE WHEN t.type1 = 3 THEN 1 ELSE 0 END) as type3_ind
FROM YourTable t
GROUP BY t.productID

This is dynamic for all productID, not just a specific one. To select a specific one, replace the group by with your WHERE clause.

This will return an indication for each product on every type if he has it(1) or not(0).

So for example for the data:

PRODUCTID | Type1
   1          1
   1          2
   1          3
   2          3

This will return:

PRODUCTID | TYPE1_IND | TYPE2_IND | TYPE3_IND
    1           1            1           1
    2           0            0           1

Or to get them all as one column use GROUP_CONCAT()

SELECT t.productID,
       GROUP_CONCAT(concat('TYPE',t.type1))
FROM YourTable t
GROUP BY t.productID

ebahi · Answer 2 · 2016-05-30T12:10:53.027

0

You can use GROUP_CONCAT :

select id productid,
       group_concat(concat('type', pricing_category) order by pricing_category) type
from product
where finish_product_id = 1
group by id;

See SQLFiddle

edited May 30 '16 at 12:10

answered May 30 '16 at 12:05

ebahi

536
2
7

How do I combine separate records into one?

2 Answers2