MySQL Forums
Forum List  »  Spanish

Re: que hay con GROUP_CONCAT
Posted by: William Chiquito
Date: August 22, 2007 05:47PM

Hola jorge,

Imagino que el "ems" es el EMS SQL Manager for MySQL.

Yo estoy usando SQLyog MySQL GUI - Community Edition v6.05.

Mi prueba retorna los datos esperados.
CREATE TABLE `t1` (
  `campo1` char(92) default NULL,
  `campo2` char(10) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `t1`(`campo1`,`campo2`) values 
		('10,03,04,12,14','unidad1'),
		('06,05,08,11,14','unidad1'),
		('14,08','unidad2'),
		('09,06,08','unidad2'),
		('19,24,14,19,26','unidad1'),
		('30,03,09,22,14','unidad1');

DELIMITER $$

DROP FUNCTION IF EXISTS `unicos`$$

CREATE FUNCTION `unicos`(cadena LONGTEXT)
	RETURNS char(92) CHARSET utf8
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
	SET @pos = 1;
	SET @result = '';
	SET @str= CONCAT(cadena, ',');
	REPEAT
	SET @item= SUBSTR(@str, @pos, 3);
	IF LOCATE(@item, @result)= 0 THEN
	SET @result = CONCAT(@item, @result);
	END IF;
	SET @pos = @pos + 3;
	UNTIL @pos >= LENGTH(@str) END REPEAT;
	RETURN SUBSTR(@result, 1, LENGTH(@result)-1); 
END$$

DELIMITER ;

SELECT campo2, unicos(GROUP_CONCAT(campo1)) FROM t1 GROUP BY campo2;
Resultado:
campo2   unicos(GROUP_CONCAT(campo1))                
-------  --------------------------------------------
unidad1  22,09,30,26,24,19,11,08,05,06,14,12,04,03,10
unidad2  06,09,08,14



Edited 1 time(s). Last edit at 08/22/2007 05:48PM by William Chiquito.

Options: ReplyQuote


Subject
Views
Written By
Posted
3115
August 21, 2007 03:13PM
3052
August 22, 2007 03:07AM
2524
August 22, 2007 01:33PM
Re: que hay con GROUP_CONCAT
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.