Search code examples
mysqlgroup-concat

How can I group_concat this mysql fields

I have this MySQL Query:

 SELECT a.orcidid 
 , GROUP_CONCAT(distinct a.`from` SEPARATOR '<>' ) as StartDate
 , GROUP_CONCAT(distinct a.`to` SEPARATOR '<>' ) as EndDate
 from orcidaffils a
 GROUP BY a.orcidid ;

For this DATA Table:

 CREATE TABLE `orcidaffils` (
 `recid` int(11) NOT NULL AUTO_INCREMENT,
 `affil` varchar(6000) DEFAULT NULL,
 `orcidid` varchar(100) DEFAULT NULL,
 `city` varchar(100) DEFAULT NULL,
 `country` varchar(100) DEFAULT NULL,
 `from` date DEFAULT NULL,
 `to` date DEFAULT NULL,
  PRIMARY KEY (`recid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

  -- ----------------------------
  -- Records of orcidaffils
  -- ----------------------------
  INSERT INTO `orcidaffils` VALUES ('2', 'Graz University of Technology', '0000-0004-1034-5187', 'Graz', 'AT', '2010-01-01', null);
  INSERT INTO `orcidaffils` VALUES ('3', 'Ecole Polytechnique', '0000-0004-1034-5187','Palaiseau', 'FR', '2008-09-01', '2010-07-01');
  INSERT INTO `orcidaffils` VALUES ('4', 'University of Würzburg', '0000-0004-1034-5187', 'Wurzburg', 'DE', '2005-09-01', '2007-12-01');

No I would like to get this output: wanted output

The question is how to group_concat that the beginndate and the enddate is merged together per affliliation.

2010-01-01-now<>2008-01-09 to 2010-01-07<>2005-01-09 to 2007-01-12

thanks for any usefull advice.

Solution

  • As per the image you've mentioned you could try something like this:

     SELECT a.orcidid ,
   GROUP_CONCAT(a.`affil` SEPARATOR '<>' )
 , GROUP_CONCAT(CONCAT(a.`from`, ' to ', IFNULL(a.`to`,'now')) SEPARATOR '<>' ) AS StartDate
 FROM orcidaffils a
 GROUP BY  a.orcidid ;