SQL: Display the table and its job by yes and no

Question

This is my table:

User Table

Id | Username |
---------------
1  | jdoe     |

Job Table

Id | Job       |
----------------
1  | Waiter    |
2  | Office    |
3  | Freelance |

User Job Table

Id |UserId | JobId |
--------------------
1  | 1     | 2     |
2  | 1     | 3     |

How to select this table and display it to look like this below:

Id | Username | Waiter| Office | Freelance|
-------------------------------------------
1  | jdoe     | No    | Yes    | Yes      |

Answer 1

This is a pretty standard pivot query question, with an additional slight twist. In case a given user does not have a certain type of job assigned, you want to display 'No'. One way to do this is to make use of COALESCE and replace NULL job aggregates.

SELECT u.Id,
       u.Username,
       COALESCE(MAX(CASE WHEN j.Job = 'Waiter'    THEN 'Yes' END), 'No') AS Waiter,
       COALESCE(MAX(CASE WHEN j.Job = 'Office'    THEN 'Yes' END), 'No') AS Office,
       COALESCE(MAX(CASE WHEN j.Job = 'Freelance' THEN 'Yes' END), 'No') AS Freelance
FROM User u
LEFT JOIN User_Job uj
    ON u.Id = uj.UserId
LEFT JOIN Job j
    ON uj.JobId = j.Id
GROUP BY u.Id,
         u.Username