Database

<br />
CREATE TABLE foo<br />
 (c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL CHECK (c2 NOT LIKE<br />
'%,%'), PRIMARY KEY (c1,c2) )<br />
<br />
INSERT INTO foo VALUES ('Tom','Car')<br />
INSERT INTO foo VALUES ('Tom','House')<br />
INSERT INTO foo VALUES ('Tom','Plane')<br />
INSERT INTO foo VALUES ('Tom','Boat')<br />
INSERT INTO foo VALUES ('Jim','House')<br />
INSERT INTO foo VALUES ('Jim','Boat')<br />
<br />
CREATE TABLE Results<br />
 (c1 VARCHAR(10) PRIMARY KEY, c2list VARCHAR(100) NOT NULL)<br />
<br />
INSERT INTO Results<br />
 SELECT c1,MAX(c2) FROM foo GROUP BY c1<br />
<br />
WHILE @@ROWCOUNT>0<br />
 UPDATE Results<br />
  SET c2list =<br />
  (SELECT MAX(c2)<br />
   FROM foo<br />
   WHERE foo.c1 = Results.c1 AND<br />
    foo.c2<LEFT(c2list,CHARINDEX(',',c2list+',')-1))<br />
   + ', ' + c2list<br />
  WHERE EXISTS<br />
  (SELECT *<br />
   FROM foo<br />
   WHERE foo.c1 = Results.c1 AND<br />
    foo.c2<LEFT(c2list,CHARINDEX(',',c2list+',')-1))<br />
<br />
SELECT * FROM Results<br />
<br />