MySQL Procedure to transform key/value pair to columns

Question

We are trying to build a de-normalised version of our database for use in Tableau. One of the challenges we have is dealing with our key/value pairs in one of our tables. See the following simplified version of some data.

Asset Table
Asset ID  Site ID   Make          Model
1         1         Toyota        Corolla
2         2         Honda         Civic
3         2         Suzuki        Swift

Asset Property Types Table
Site ID  Asset Property Name    Asset Property Type
1        Odometer               Numeric
1        Registration           Text
1        Expiry Date            Date
2        Odometer               Numeric
2        Registration           Text
2        Expiry Date            Date
2        Colour                 Text


Asset Properties Table
Asset ID  Key            Text Value   Numeric Value    Date Value
1         Odometer                    1234  
1         Registration   ABC123  
1         Expiry Date                                  2018-02-08  
2         Odometer                    1255  
2         Registration   ABC124
2         Colour         Red 
2         Expiry Date                                  2018-01-08  
3         Registration   ABC125
3         Odometer                    1266
3         Colour         Blue
3         Expiry Date                                  2018-03-25  

Some points to note on this. This is a simplified version of the data. Not all Assets will have the same key/value pairs. Sites will have individual data types that they want to store which may be different to other sites.

Ultimately here is what we want to try and achieve:

Site 1 Asset Table
Asset ID   Make        Model    Odometer     Registration   Expiry Date
1          Toyota      Corolla  1234         ABC123         2018-02-08

Site 2 Asset Table
Asset ID   Make        Model    Odometer     Registration   Expiry Date   Colour
2          Honda       Civic    1255         ABC124         2018-01-08    Red
3          Suzuki      Swift    1266         ABC125         2018-03-25    Blue

My general approach to this was the following:

1. Create the Site Specific Asset Tables with just the Make & Model and populate them.
2. Use a loop to ALTER the table adding a column for each of the Asset Properties that exist
3. Populate the Site Specific Asset table with the Asset Property data in the appropriate columns

I was hoping to do all of this with a MySQL Procedure so that I can schedule it to run automatically every hour and replace the various Site level tables. A CASE statement will not work as this needs to be dynamic.

I really appreciate any advice/help on how to achieve this. Whilst I'm okay with SQL, procedures are way out of my depth.

Answer 1

I guess the difficult bit is building dynamic case statements. So given your data (with some changes to remove white spaces and possible key word clashes)

create table Asset(Asset_ID int, Site_ID int,  Make varchar(20), Model varchar(20));
insert into asset values
(1  ,       1  ,       'Toyota' ,       'Corolla'),
(2  ,       2  ,       'Honda'  ,       'Civic'),
(3  ,       2  ,       'Suzuki' ,       'Swift');

drop table if exists Asset_Property_Types;
create table Asset_Property_Types (Site_ID int, Asset_Property_Name varchar(100),   Asset_Property_Type varchar(100));
insert into asset_property_types values
(1   ,    'Odometer'       ,        'Numeric_value'),
(1   ,    'Registration'   ,        'Text_value'),
(1   ,    'Expiry_Date'    ,        'Date_value'),
(2   ,    'Odometer'       ,        'Numeric_value'),
(2   ,    'Registration'   ,        'Text_value'),
(2   ,    'Expiry_Date'    ,         'Date_value'),
(2   ,    'Colour'         ,         'Text_value');


create table Asset_Properties(Asset_ID  int , asset_property_name varchar(30),Text_Value varchar(100),   Numeric_Value int ,   Date_Value date);
insert into asset_properties values
(1    ,     'Odometer'      , null      ,      1234  ,null),
(1    ,     'Registration'  , 'ABC123'  ,null        ,null),
(1    ,     'Expiry_Date'   , null      ,        null, '2018-02-08'),  
(2    ,     'Odometer'      , null      ,      1255  ,null),
(2    ,     'Registration'  , 'ABC124'  ,null        ,null),
(2    ,     'Colour'        , 'Red'     ,null        ,null),
(2    ,     'Expiry_Date'   , null      , null       ,'2018-01-08'),  
(3    ,     'Registration'  , 'ABC125'  ,null        ,null),
(3    ,     'Odometer'      , null      ,        1266,null),
(3    ,     'Colour'        , 'Blue'    ,null         ,null),
(3    ,     'Expiry_Date'   , null      , null        ,'2018-03-25');  

This code

    set @sql = (select concat('select a.asset_id ,a.site_id,a.make,a.model,',
             group_concat(
            concat('max(case when asset_property_name  = ' ,char(39),asset_property_name,char(39), ' then ' ,asset_property_type ,' else null end) as ', asset_property_name)
            )
            ,' from asset a join asset_properties ap on ap.asset_id = a.asset_id group by a.site_id,a.asset_id;'
            )
    from 
    (select distinct asset_property_name,asset_property_type from asset_property_types) a
    )
    ;

builds this sql statement

select a.asset_id ,a.site_id,a.make,a.model,
        max(case when asset_property_name  = 'Odometer' then Numeric_value else null end) as Odometer,
        max(case when asset_property_name  = 'Registration' then Text_value else null end) as Registration,
        max(case when asset_property_name  = 'Expiry_Date' then Date_value else null end) as Expiry_Date,
        max(case when asset_property_name  = 'Colour' then Text_value else null end) as Colour 
from asset a join asset_properties ap on ap.asset_id = a.asset_id 
group by a.site_id,a.asset_id;

Which you can then submit to sql like so

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;