Mysql – Group by specific value and return results in generated column

MySQL

I am attempting to figure out how I can pull this off, but I have no clue how to do it.

I have a table (below) that has object_id's, types and values. The values are always unique and the type is always type1, type2 or type3.

id   object_id   type    value
---------------------------------
1    1           type1   123
2    1           type2   456
3    2           type3   789

The end result I'm trying to get is to consolidate all object_id's and return the types associated in "virtual columns" which are type1, type2, and type3.

object_id   type1  type2  type3
-----------------------------
   1        123    456    null
   2        null   null   789

Not sure if this is possible in mySql or if i should just try and build this in PHP instead. Any help would be greatly appreciated

Best Answer

create table test (
 id int,
 object_id int,
 type varchar(5),
 val  int
);

insert into test values (1,1,'type1',123);
insert into test values (2,1,'type2',456);
insert into test values (3,2,'type3',789)

select t.object_id,
       max(t.type1) type1,
       max(t.type2) type2,
       max(t.type3) type3
  from (
 select object_id,
        case
        when type = 'Type1' then val
        end Type1,
        case
        when type = 'Type2' then val
        end Type2,
        case
       when type = 'Type3' then val
       end Type3
  from test
 ) t
group by t.object_id

db fiddle

If you have more then one record for the same id, object_id, and type, this will take the max, which may or may not be what you need.

Related Question