MySQL show data from Rows to columns

Question

I am stuck at getting a query with joining 2 tables. Below is the database structure. What I want to achieve is to list all the information from one table with join on the second table but it has 3 conditions. I have written the following SQL but it's not working.I want only 2 rows returning all rows data in each 3 columns. Please advice.

Tables are:

My SQL:

SELECT b.*,
       CASE WHEN pc.caster_type = '1' THEN pc.name END as  primary_caster ,
       CASE WHEN pc.caster_type = '2' THEN pc.name END as  secondary_caster ,
       CASE WHEN pc.caster_type = '3' THEN pc.name END as  caster  
FROM broadcasts b 
left join rel_braodcast_casters pc ON pc.fk_broadcast_id = b.broadcast_id ;

Getting this result:

I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.

Thanks in advance.

Answer 1

Finally I got the answer. Below is the fiddle.

sqlfiddle.com/#!9/83f530/6 or sqlfiddle.com/#!9/83f530/7

Thanks to Akina who helped me a lot. I will prefer to use single join SQL over multiple join fiddle.