What is the correct code to find identify dupe rows and merge them together?

userp2t61 0
Viewed 39

I have this SQL data pull that currently has dupe rows per product. In these dupe rows, some of the PKs per product are the same but what's different is the discount. The discounts will be different per product. For eg. Product A is listed twice on the pull but has a varying discount of .10 vs .15 for example:

1

I want to be able to merge both rows into one row and make it look like this: 2

What does the code look like for that

mysql
1 Answers

This assumes PK will only have 2 rows.

CREATE TABLE table1 (
  id INTEGER ,
  discount int
);


INSERT INTO table1 VALUES (0001, 20);
INSERT INTO table1 VALUES (0001, 10);
INSERT INTO table1 VALUES (0045, 15);
INSERT INTO table1 VALUES (0045, 35);


SELECT id, substring_index(dl, ',', 1) as `regular`, substring_index(dl, ',', -1) as `vip`
FROM (
  SELECT id, group_concat(discount order by discount) dl
  FROM table1 GROUP BY id
) t
Phaelax z0
Related