SQL Select parent as column name and child as value

I am creating a database to store music.

There are different categories that each have sub categories. Example:

id    name                  parentID
1     instrumentation       null
2     solo_instrument       null
3     Concert Band          1
4     Brass Band            1
5     Fanfare Band          1
6     Clarinet              2
7     Saxophone             2
8     Trumpet               2

On the other hand I have a table that stores the musicID that is linked to a categoryID

id    categoryID  musicID
1     4           1
2     8           1
3     3           2
4     6           2

I need the following result from a query:

musicID    instrumentation    solo_instrument
1          Brass Band         Trumpet
2          Concert Band       Clarinet

I have been told to use a tree structure as in the future it is likely that other categories are added and this should be able to support that. However, I am not able to figure out how to write a query to get the result above.

I kind of get the result I want when selecting first the instrumentation, second the solo_instrument, but this is all hardcoded and does not allow for music tracks to only have one parentID as I select them individually.

Is this even possible or should I overhaul my database structure? I’d like to see your recommendations.

Answer

You should be able to tackle this using conditional aggregation.

Query :

SELECT
    mu.musicID,
    MAX(CASE WHEN cp.name = 'instrumentation' THEN ca.name END) instrumentation,
    MAX(CASE WHEN cp.name = 'solo_instrument' THEN ca.name END) solo_instrument
FROM
    musics mu
    INNER JOIN categories ca ON ca.id = mu.categoryID
    INNER JOIN categories cp ON cp.id = ca.parentID
GROUP by mu.musicID

The INNER JOINs pull up the corresponding category, and then goes up one level to find the parent category. If new root categories are created, you would just need to add more MAX() columns to the query.

In this DB Fiddle demo with your sample data, the query returns :

| musicID | instrumentation | solo_instrument |
| ------- | --------------- | --------------- |
| 1       | Brass Band      | Trumpet         |
| 2       | Concert Band    | Clarinet        |