user2302158 user2302158 - 3 days ago 5
MySQL Question

How to combine based on id in mysql

Please help to find out the result.How to a write a query to combine rows which has same id

id size sizeorder color colororder
1 M 1 null null
1 null null red 1
2 s 1 null null
2 Null null green 2


output should be

id size sizeorder color colororder
1 M 1 red 1
2 s 1 green 2

Answer
SELECT id,
       MAX(size) AS size,
       MAX(sizeorder) AS sizeorder,
       MAX(color) AS color,
       MAX(colororder) AS colororder
FROM yourTable
GROUP BY id

The rollup you are trying to do is similar to what happens in a pivot query. The "secret sauce" in the above query is that MySQL's MAX function ignores NULL values. This means that in your table only the non NULL values will be retained in each column, for each grouped id.

Comments