Modify/flatten result from sql one to many query with multiple joins

Question

Consider the following simplified schema example -

sys_systems

id   name
---------------------
1    Inventory


sys_shadow_data

id   primary_key_value   sys_schema_id   field_value
---------------------------------------------------------
1    1                   143             Awaiting audit
2    1                   144             90


sys_schemas

id      field_name
-------------------
143     notes
144     status

The following query -

select sys_systems.*, 
(select sys_shadow_data.field_value where sys_schemas.field_name = 'status') as status, 
(select sys_shadow_data.field_value where sys_schemas.field_name = 'notes') as notes 
from sys_systems
    left join sys_shadow_data
        on sys_systems.id = sys_shadow_data.primary_key_value
            left join sys_schemas
                on sys_schemas.id = sys_shadow_data.sys_schema_id

yields -

id   name       status    notes
-----------------------------------------
1    Inventory  90        null
1    Inventory  null      Awaiting audit

The result I would like to obtain is -

id   name       status    notes
-----------------------------------------
1    Inventory  90        Awaiting audit

Is there a change I can make to the sql query to achieve this or is this a case whereby I would need to process the results in the application code?

Answer 1

Your query amounts to pivoting the joined tables on the field_name column from the sys_schemas table to generate new columns for each value in that column. You can GROUP BY the inventory ID and name, then use MAX to collapse the two records into one:

SELECT sys_systems.id,
       sys_systems.name,
       MAX(CASE WHEN sys_schemas.field_name = 'status'
                THEN sys_shadow_data.field_value ELSE NULL END) AS status, 
       MAX(CASE WHEN sys_schemas.field_name = 'notes'
                THEN sys_shadow_data.field_value ELSE NULL END) AS notes 
FROM sys_systems
LEFT JOIN sys_shadow_data
    ON sys_systems.id = sys_shadow_data.primary_key_value
LEFT JOIN sys_schemas
    ON sys_schemas.id = sys_shadow_data.sys_schema_id
GROUP BY sys_systems.id,
         sys_systems.name