Create Temporary Table Where CONCAT GROUPS are placed into their own columns?

I'm a bit of a MYsql newbie and I'm stumped as to how to do this. I think I have to insert my results into a TEMPORARY TABLE, but I'm not sure.

I’d like to output a report from my database that shows each music album I have in 开发者_如何学JAVAmy collection (album1, album2, album3) with a separate column for each format the album is available in (WAV, CD, cassette, etc). I'm not sure how to take the many-to-one relationship between format_types and album and output a report where each format has a column and each album has only one row.

I have three tables (albums, format_types, and occurrences)

albums 
id      uuid
1       d2ec45e9-5fcc
2       949ebf32-8c95
3       98c7cc7e-ebe4

format_types
id  name
1   cassette
2   CD
3   DAT
4   WAV

album_occurrences
id  album_id format_type_id
1   1        1
2   2        2
3   3        3
4   2        3
5   3        1
6   1        2
7   1        3
8   1        4
9   2        4

The result I’d like is:

albums_by_format
album_id    format_name_1   format_name_2   format_name_3   format_name_4
1           Cassette        CD              DAT             WAV
2           NULL            CD              NULL            WAV
3           Cassette        NULL            DAT             NULL

I'm wondering the best method to return a result like the above?

---

The below should give you what you need. I say "should" because I haven't tested it.

SELECT `al`.`id`,
    (
    SELECT `ft`.`name`
    FROM `format_types` `ft`
    INNER JOIN `album_occurrences` `ao` ON `ft`.`id` = `ao`.`format_type_id`
    WHERE `ao`.`album_id` = `al`.`id`
    AND `ft`.`id` = 1
    ) `format_name_1`,
    (
    SELECT `ft`.`name`
    FROM `format_types` `ft`
    INNER JOIN `album_occurrences` `ao` ON `ft`.`id` = `ao`.`format_type_id`
    WHERE `ao`.`album_id` = `al`.`id`
    AND `ft`.`id` = 2
    ) `format_name_2`,
    (
    SELECT `ft`.`name`
    FROM `format_types` `ft`
    INNER JOIN `album_occurrences` `ao` ON `ft`.`id` = `ao`.`format_type_id`
    WHERE `ao`.`album_id` = `al`.`id`
    AND `ft`.`id` = 3
    ) `format_name_3`,
    (
    SELECT `ft`.`name`
    FROM `format_types` `ft`
    INNER JOIN `album_occurrences` `ao` ON `ft`.`id` = `ao`.`format_type_id`
    WHERE `ao`.`album_id` = `al`.`id`
    AND `ft`.`id` = 4
    ) `format_name_4`
FROM
`albums` `al`;

Let me know if it works :-)