CREATE TABLE `person` (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR(255) NOT NULL
) ENGINE = MYISAM;
 
INSERT INTO `person` VALUES (1, 'Si A');
INSERT INTO `person` VALUES (2, 'Si B');
INSERT INTO `person` VALUES (3, 'Si C');
 
CREATE TABLE `hobby`(
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR(255) NOT NULL
) ENGINE = MYISAM;
 
INSERT INTO `hobby` VALUES (1, 'Menyelam');
INSERT INTO `hobby` VALUES (2, 'Tidur');
INSERT INTO `hobby` VALUES (3, 'Jalan-Jalan');
INSERT INTO `hobby` VALUES (4, 'Makan');
 
CREATE TABLE `person_hobby`(
`id_person` INT(11) NOT NULL,
`id_hobby` INT(11) NOT NULL,
PRIMARY KEY (id_person, id_hobby)
) ENGINE = MYISAM;
 
INSERT INTO `person_hobby` VALUES (1, 1);
INSERT INTO `person_hobby` VALUES (1, 2);
INSERT INTO `person_hobby` VALUES (1, 4);
INSERT INTO `person_hobby` VALUES (2, 3);
INSERT INTO `person_hobby` VALUES (2, 2);

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `belajar`.`person_hobby` $$
CREATE PROCEDURE `belajar`.`person_hobby` ()
BEGIN
  DECLARE l_person VARCHAR(255);
  DECLARE l_hobby  VARCHAR(255);
  DECLARE temp_p VARCHAR(255) DEFAULT '';
  DECLARE temp_h TEXT DEFAULT '';
  DECLARE result TEXT DEFAULT '';
  DECLARE done  INT DEFAULT 0;
 
  DECLARE cur1 CURSOR FOR
    SELECT person.name, hobby.name
    FROM person, hobby, person_hobby
    WHERE person.id = person_hobby.id_person AND hobby.id = person_hobby.id_hobby
    ORDER BY person.name ASC;
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
 
  DROP TEMPORARY TABLE IF EXISTS person_hobby_raises;
  CREATE TEMPORARY TABLE person_hobby_raises (name VARCHAR(255), hobby TEXT);
 
  OPEN cur1;
  emp_loop:LOOP
 
    FETCH cur1 INTO l_person,l_hobby;
 
    IF done=1 THEN
      LEAVE emp_loop;
    END IF;
 
    IF temp_p='' THEN
      SET temp_p = l_person;
    END IF;
 
    IF temp_p=l_person THEN
      IF temp_h='' THEN
        SET temp_h = l_hobby;
      ELSE
        SET temp_h = CONCAT(temp_h,', ',l_hobby);
      END IF;
    ELSE
      INSERT INTO person_hobby_raises VALUES (temp_p, temp_h);
      SET temp_h = l_hobby;
    END IF;
 
    SET temp_p = l_person;
  END LOOP emp_loop;
  CLOSE cur1;
 
  INSERT INTO person_hobby_raises VALUES (temp_p, temp_h);
 
  COMMIT;
 
END $$
 
DELIMITER ;

CALL person_hobby();
 
SELECT * FROM person_hobby_raises;