MySQL Forums
Forum List  »  Spanish

Re: que hay con GROUP_CONCAT
Posted by: William Chiquito
Date: August 21, 2007 03:13PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: que hay con GROUP_CONCAT
3114
August 21, 2007 03:13PM
3052
August 22, 2007 03:07AM
2524
August 22, 2007 01:33PM
2737
August 22, 2007 05:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.