Mysql: How to merge multiple rows to return only one row

Question

I have a sql table having multiple rows and i want to merge some of his rows to be in one row, if row columns different then return 'Mixed Value' else return the column value

Example:

SQL Table

I want the result to be like that if i select row with id 1 and 2

Table Resutls

and if i select row with id 1,2 and 3

Table Resutls

Thank's in advance

See Why should I provide a Minimal Reproducible Example for a very simple SQL query? ... We cant reproduce data easy from images.. — Raymond Nijland
– Raymond Nijland, Commented Jun 11, 2019 at 11:20
If you select row 1 and 2 why is product_sku in your result mixed_value when in the data the product-sku is the same? — P.Salmon
– P.Salmon, Commented Jun 11, 2019 at 11:41

ScaisEdge · Accepted Answer · 2019-06-11 11:20:43Z

1

You could try using group_concat(distinct() )

SELECT GROUP_CONCAT(DISTINCT(firstname) SEPARATOR ' ') ,  
       GROUP_CONCAT(DISTINCT(lastname) SEPARATOR ' ')
FROM table

answered Jun 11, 2019 at 11:20

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Pinx0 · Accepted Answer · 2019-06-11 11:24:37Z

0

You can use a combination of CASE WHENand COUNT(DISTINCT)to get what you want. It isn't clear if you want to group by price_id or by service_id, but I will assume so.

SELECT MIN(id) id, 
CASE WHEN COUNT(DISTINCT firstname) > 1 THEN 'MIXED_VALUE' ELSE MIN(firstname) END firstname,
CASE WHEN COUNT(DISTINCT lastname) > 1 THEN 'MIXED_VALUE' ELSE MIN(lastname) END lastname,
price_id, service_id,
CASE WHEN COUNT(DISTINCT product_sku) > 1 THEN 'MIXED_VALUE' ELSE MIN(product_sku) END product_sku
FROM yourTable
GROUP BY price_id, service_id

answered Jun 11, 2019 at 11:24

Pinx0

1,25818 silver badges28 bronze badges

1 Comment

Aymane Mzily Over a year ago

Why group by price_id, service_id? i want also to make the same thing on it

Collectives™ on Stack Overflow

Mysql: How to merge multiple rows to return only one row

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related