Join a same table using key and values to get data in one row

Question

This is my table structure,

i wabt to combine the same table with its key values like prop_key ='color'

id    journal_id     property     prop_key       old_value                        value
405   252             attr         color         1                                 0
372   252             attr       updated_at      2017-03-18 03:43:34 UTC     2017-03-18 03:43:34 UTC
402   252             attr       sprint_id       5                                 0

I want the result in this form.

id           color       sprint_id           start_date
372           2           5               2017-03-18 03:43:34 UTC  

I try the following

select t.id,
       max(case when a.prop_key='color' then a.value end) attr_val1,
       max(case when a.prop_key='sprint_id' then a.value end) attr_val2,
       max(case when a.prop_key='updated_at' then a.value end) attr_val3
from journal_details t
left join journal_details a on t.id = a.id
where t.journal_id=242 
group by t.id

Select 
    journal_details.id,  
    a1.value as color,
    a2.value as sprint_id,
    a3.value as start_date
from journal_details
    left join (select * from journal_details where prop_key='color') a1 on journal_details.id=a1.id
    left join (select * from journal_details where prop_key='sprint_id') a2 on journal_details.id=a2.id
    left join (select * from journal_details where prop_key='start_date') a3 on journal_details.id=a3.id
    where journal_details.journal_id=242

But i didnot get satisfactory result.

Answer 1

You don't need a self join.

Try this:

select min(id),
    max(case when prop_key = 'color' then value end) attr_val1,
    max(case when prop_key = 'sprint_id' then value end) attr_val2,
    max(case when prop_key = 'updated_at' then value end) attr_val3
from journal_details
where journal_id = 242