CODEWITHSUNDEEP
mysqldatabase
user2302158
user2302158

Reputation: 463

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

Upvotes: 0

Views: 43

Answers (2)

Jens
Jens

Reputation: 69460

You have to join your tables and use case when to get the value which is not null:

select case when t1.size is null then t2.size else t1.size end as size, 
  case when t1.sizeorder is null then t2.sizeorder else t1.sizeorder end as sizeorder,
  case when t1.color is null then t2.color else t1.color end as color 
  case when t1.colororder is null then t2.colororder else t1.colororder end as colororder 

from <table> t1 join <table> t2 on t1.id = t2.id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

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.

Upvotes: 2

Related Questions