Tuesday, July 31, 2012

Get distinct value, show multiple row values in one column


If there is a table called userList

userid       listid
----------      -------------
1               2
2               2
1               2
1               1
2               1
2 3
2 1

Result I expected was:
userid       listid
----------      -------------
1               1,2,2
2               1,1,2,3

SELECT userid, GROUP_CONCAT(DISTINCT listid ORDER BY listid ASC SEPARATOR ',') FROM mahesh GROUP BY userid;


Table Used :

CREATE TABLE IF NOT EXISTS `mahesh` (
  `userid` int(11) NOT NULL,
  `listid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mahesh` (`userid`, `listid`) VALUES
(1, 2),
(2, 2),
(1, 2),
(1, 1),
(2, 1),
(2, 3),
(2, 1);