0

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

0
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.

0
SELECT 
   object_id,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type1') as Type1,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type2') as Type2,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type3') as Type3
FROM below b0

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=98618f2a316933c6c73260b13e33f15c or

SELECT 
  b0.object_id,
  b1.value as Type1,
  b2.value as Type2,
  b3.value as Type3
FROM below b0
LEFT JOIN below b1 ON b1.object_id=b0.object_id and b1.type='type1'
LEFT JOIN below b2 ON b2.object_id=b0.object_id and b2.type='type2'
LEFT JOIN below b3 ON b3.object_id=b0.object_id and b3.type='type3'

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0437e739f9a9d4cd9923feae6ecd5593

  • Add grouping and aggregate functions to the last query. fiddle. 1st may be modified by such way too, but it looks like a bad practice example. – Akina Oct 12 '19 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.