Hola Diego,
Por la naturaleza dinámica de lo que necesitas, se me ocurre un rutina almacenada (stored procedure) como el siguiente:
SELECT VERSION();
Result:
VERSION()
-------------------
5.0.45-community-nt
DELIMITER $$
DROP PROCEDURE IF EXISTS `testsp`$$
CREATE PROCEDURE `testsp`()
BEGIN
DECLARE done, tmonto, control INT DEFAULT 0;
DECLARE tcolumn VARCHAR(3);
DECLARE tcolumns VARCHAR(1000) DEFAULT 'movimiento CHAR(20),';
DECLARE tmovimiento, tplaza VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT plaza FROM plaza;
DECLARE cur2 CURSOR FOR SELECT m.movimiento, m.monto, p.plaza
FROM movimiento m
INNER JOIN plaza p on m.idplaza = p.idplaza;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO tcolumn;
IF NOT done THEN
SET control = 1;
SET tcolumns = CONCAT(tcolumns, tcolumn, ' INT,');
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
IF control = 1 THEN
SET done = 0;
SET control = 0;
DROP TEMPORARY TABLE IF EXISTS temporal;
SET tcolumns = LEFT(tcolumns, CHAR_LENGTH(tcolumns) - 1);
SET @qry = CONCAT('CREATE TEMPORARY TABLE temporal (', tcolumns, ') ENGINE=MEMORY');
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
OPEN cur2;
REPEAT
FETCH cur2 INTO tmovimiento, tmonto, tplaza;
IF NOT done THEN
SET control = 1;
SET @qry = CONCAT('SELECT COUNT(movimiento) INTO @contador FROM temporal
WHERE movimiento = ''', tmovimiento, '''');
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;
IF @contador = 0 THEN
SET @qry = CONCAT('INSERT INTO temporal (movimiento, ', tplaza, ') VALUES
(''', tmovimiento, ''',', tmonto, ')');
PREPARE stmt2 FROM @qry;
EXECUTE stmt2;
ELSE
SET @qry = CONCAT('UPDATE temporal SET ', tplaza , ' = ', tmonto, '
WHERE movimiento = ''', tmovimiento, '''');
PREPARE stmt2 FROM @qry;
EXECUTE stmt2;
END IF;
END IF;
UNTIL done END REPEAT;
IF control = 1 THEN
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE stmt1;
END IF;
CLOSE cur2;
SELECT * FROM temporal;
DROP TEMPORARY TABLE IF EXISTS temporal;
END IF;
END$$
DELIMITER ;
Edited 3 time(s). Last edit at 12/05/2007 02:25PM by William Chiquito.