0
votes

I want to convert the SQL statement of picture 1 to have the result in picture 2.

Some constraints: it has to be dynamic so hardcoding the names in the statement would not be good. One other row might have 5 people assigned to it instead of 3.

Output of SQL result:

Output of SQL result

SQL Statement producing the result shown in picture 1:

select 
    t.*, e.*, 
    p.person_days, m.net_md_rate_eur 
from 
    tpl_tasks t 
join 
    person_days p on t.id = p.id 
join 
    employees e on e.employee_id = p.employee_id 
                and e.type != 'External' 
join 
    md_rates m on e.bu_or_it = m.type 
union
select 
    t.*, e.*, 
    p.person_days, m.net_md_rate_eur 
from 
    tpl_tasks t 
join
    person_days p on t.id = p.id 
join 
    employees e on e.employee_id = p.employee_id 
                and e.type = 'External' 
join 
    md_rates_externals m on e.employee_id = m.employee_id 
order by 
    tpl_project

Desired result:

Desired SQL Result

Any ideas are helpful.

What if one TPL Task ID has 8 people and another TPL Task ID has 3? You need 8 columns, but in the second row only 3 get filled out? I can't help but think that maybe instead of dynamic number of dynamically named columns you just throw STRING_AGG() at it and have two columns, one that string_aggs name and the other string_aggs man days. It's not as pretty, but is significantly less complex to write. - JNevill