SQL How to select from multiple values in joined table

I have two tables A and B joined with a common id field. Table A has single entries per id. Table B has multiple entries per id. Table B has two columns “name” and “customertype”. I want to be able to SELECT columns from A and B but I want to show the column “name” from Table B three times: once for each separate match of customertype.

Example:

I want to be able to SELECT and display:

id,country,name(from EC),date,name(from T1),name(from T2)

How do I do this? I am a novice SQL user! Thanks in advance for advice.

Answer

You can use conditional aggregation:

select a.id, a.country, a.date,
       max(case when customertype = 'EC' then name end) as ec,
       max(case when customertype = 'T1' then name end) as t1,
       max(case when customertype = 'T2' then name end) as et2
from a join
     b
     on a.id = b.id
group by a.id, a.country, a.date