How to flatten Rows into Column (Field) Values

I have data that has Job_ids split across multiple rows (i’ve had to do this because previously i’ve had to split out team members from each job_id, then identify their teams

The format my data is currently in is as follows, (not shown) but each job_id has an employee who has a team assigment, and therefore for each row there is a yes in only one of the columns:

+--------------+----------+--------+------------+
| job_id       | team1    | team2  | team3      |
+--------------+----------+--------+------------+
|            1 |       NO | NO     | YES        |
|            1 |      YES | NO     | NO         |
|            2 |      YES | NO     | NO         |
+--------------+----------+--------+------------+

And i’d like to ‘flatten it’ by Job_id like this to get all the yes’ to ‘override’ the No values so that I can see in one single job line, which teams are working the job:

+--------------+----------+--------+------------+
| job_id       | team1    | team2  | team2      |
+--------------+----------+--------+------------+
|            1 |       YES| NO     | YES        |
|            2 |      YES | NO     | NO         |
+--------------+----------+--------+------------+

Answer

You can use aggregation:

select 
    job_id,
    max(team1) team1,
    max(team2) team2,
    max(team3) team3
from mytable
group by job_id

This works because, string-wise, 'Y' is greater than 'N‘.

Advertisement

Answer