Key value table to view with variable columns

I have my data in a key-value table (in MySql) which has the shape:

id, key, value

Now for export to my users I need to transform it into a table with all the keys as columns. (actually only ~20 of the ~100 keys need to be in that table)

T开发者_运维技巧he workflow would be to provide it my users so that they can correct the table and to reimport the tables.

I'm just in writing a quite complex select command to give me such a table. It already is some heck of long command and I hope it will not need to be debugged.

I can't help but think this should be an already solved problem ;)

So I'm hoping anyone can provide me with some clues.

---

So far I hacked together a script that will construct a view.

DROP PROCEDURE IF EXISTS view_test;
DELIMITER //
CREATE PROCEDURE view_test()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE curr_prop VARCHAR(31);
DECLARE curr_table VARCHAR(31);
DECLARE fixed_prop VARCHAR(31);
DECLARE statement_a LONGTEXT DEFAULT 'SELECT pd.id,t0.date';
DECLARE statement_b LONGTEXT DEFAULT "FROM xxx.codes AS pd INNER JOIN    
     xxx.eav AS t0 ON (pd.id = t0.idX)";
DECLARE n_prop INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT DISTINCT prop FROM xxx.eav LIMIT 59;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

main_loop: LOOP
FETCH cur1 INTO curr_prop;   
IF done
    THEN LEAVE main_loop;
END IF; 
SET fixed_prop = curr_prop;
SET fixed_prop = REPLACE(fixed_prop,'-','_');
SET fixed_prop = REPLACE(fixed_prop,'+','_');
SET fixed_prop = REPLACE(fixed_prop,' ','_');
SET n_prop = n_prop + 1;

SET curr_table = CONCAT('t',n_prop);
SET statement_a = CONCAT(statement_a,',\n',curr_table,'.value AS ', fixed_prop);
SET statement_b = CONCAT(statement_b,' \nLEFT JOIN xxx.eav AS ',curr_table,
    ' ON (',curr_table,'.idX=pd.id AND t0.date=',curr_table,'.date AND    
    ',curr_table,'.prop="',curr_prop,'")');    
END LOOP;
-- cleanup
CLOSE cur1;

SET @S = CONCAT('CREATE OR REPLACE VIEW auto_flat_table AS\n',statement_a,
 '\n',statement_b,
  '\nGROUP BY pd.id,t0.date'); -- ,'\nGROUP BY pd.id'

PREPARE stmt_auto_demo FROM @S;
EXECUTE stmt_auto_demo;
DEALLOCATE PREPARE stmt_auto_demo;
END //
DELIMITER ;

CALL view_test();

That will create such a view for myself - though I'm a bit confused why this GROUP BY is neccesary.

Not being a SQL developer I'm pretty sure I took the long way in this program.

Also that I'm only able to use 61 tables for joins is a bummer. I think we will soon need more.