0

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)

The 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.

bdecaf
  • 4,652
  • 23
  • 44
  • 1
    Long queries with lots of joins are to be expected when using an [EAV data model](http://en.wikipedia.org/wiki/Entity-attribute-value_model). – Joe Stefanelli Sep 29 '11 at 14:36
  • 1
    See http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema – Galz Sep 29 '11 at 17:01
  • ah - EAV is the keyword I was looking for. Anyway still hard for me to do. – bdecaf Sep 30 '11 at 11:35

1 Answers1

0

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.

bdecaf
  • 4,652
  • 23
  • 44