Hola Jorge,
Se me ocurre el siguiente stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `gettest`$$
CREATE PROCEDURE `gettest`()
BEGIN
DECLARE done INT DEFAULT 1;
DECLARE a CHAR(92);
DECLARE b CHAR(10);
DECLARE counter INT DEFAULT 1;
DECLARE cur1 CURSOR FOR SELECT campo2, GROUP_CONCAT(campo1) FROM t1 GROUP BY campo2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0;
CREATE TEMPORARY TABLE IF NOT EXISTS tempt1 ENGINE = MEMORY SELECT * FROM t1 LIMIT 0;
OPEN cur1;
FETCH cur1 INTO b, a;
WHILE done DO
REPEAT
SET @qry = CONCAT('SELECT ELT(', counter, ', ', a, ') INTO @campo2');
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;
IF @campo2 IS NOT NULL THEN
INSERT INTO tempt1 VALUES (b, IF(@campo2 < 10, CONCAT('0', @campo2), @campo2));
END IF;
SET counter = counter + 1;
UNTIL @campo2 IS NULL END REPEAT;
DEALLOCATE PREPARE stmt1;
SET counter = 1;
FETCH cur1 INTO b, a;
END WHILE;
SELECT campo1, GROUP_CONCAT(DISTINCT campo2 ORDER BY campo2) FROM tempt1 GROUP BY campo1;
DROP TEMPORARY TABLE IF EXISTS tempt1;
END$$
DELIMITER ;
Edited 1 time(s). Last edit at 08/21/2007 03:16PM by William Chiquito.