SQL Query to Join Two Tables where data in one table is transposed-CodePudding

I'm trying to join two tables but not getting the desired results. Im new to SQL and will need help to joining these two tables.

The data in Table 2 is transposed and the table will only be populated only when the "value" field is populated with random values.

Details of Table 1 and Table 2 and expected output

CodePudding user response：

You could use CTEs to split up the impact from outcome. Also, your screenshot had an error for task_id 6.

with t_impact as (
  select task_id, value
  from table2
  where name = 'task_impact'
  ), 
t_outcome as (
  select task_id, value
  from table2
  where name = 'task_outcome'
  )
select distinct t1.id, 
  t1.title, 
  i.value as task_impact, 
  o.value as task_outcome
from table1 t1
left join t_impact i
  on t1.id = i.task_id
left join t_outcome o
  on t1.id = o.task_id
order by t1.id

DB-fiddle found here.