How do you combine SQL queries and display horizontally?

Question

I'm looking for a simple example of joining SQL queries and displaying results on same row.

Take the query below. This returns all programs that are 'active':

select u.first_name, u.last_name, p.programme_title
from applications a 
left join users u on u.id = a.user_id
left join programmes p on p.id = a.programme_id
where a.active = 1;


first_name last_name programme_title
------------------------------------
John       smith     programme 1

What I also want is to display programs that are also inactive:

first_name last_name programme_title programme_title2 (inactive)
-----------------------------------------------------
John       smith     programme 1     programme 2

What is the best way to achieve this? I've tried unions, but that will put programme 2 on a new row with duplicate data eg:

first_name last_name programme_title
------------------------------------
John       smith     programme 1
John       smith     programme 2

Answer 1

You can use a conditional aggregation

select u.first_name, u.last_name
    , max(case when a.active = 1 then p.programme_title end) programme_title
    , max(case when a.active <> 1 then p.programme_title end) programme_title_inactive
from applications a 
left join users u on u.id = a.user_id
left join programmes p on p.id = a.programme_id
group by u.first_name, u.last_name