I think this will do what you want. The following example assumes that the current table name is 'Table1' and the main field name is 'Field1'.
First it seperates 'Field1' into 3 fields in a temporary table (#TempTable) based upon the spaces in the field. Next it creates a finishing table (#TempFinish) with the same 3 fields as #TempTable. Finally it scrolls through #TempTable and either inserts a new record into #TempFinish if one doesn't already exist for the BeginningField and MiddleField or if one already exists then it concatenates the latest EndField onto the end of the existing one in #TempFinish.

I hope this helps (and makes sense) :)
Jake

SELECT SUBSTRING(Field1,1,CHARINDEX(CHAR(32), Field1)-1) AS 'BeginningField',
	SUBSTRING(Field1, 
		  CHARINDEX(CHAR(32), Field1)+1, 
		  CHARINDEX(CHAR(32), Field1, CHARINDEX(CHAR(32), Field1)+1) - CHARINDEX(CHAR(32), Field1)-1
		) AS 'MiddleField',
	SUBSTRING(Field1, 
		  CHARINDEX(CHAR(32), Field1, CHARINDEX(CHAR(32), Field1)+1) + 1,
		  LEN(Field1) - CHARINDEX(CHAR(32), CHARINDEX(CHAR(32), Field1, CHARINDEX(CHAR(32), Field1)+1))
		) AS 'EndField'
INTO #TempTable
FROM Table1

CREATE TABLE #TempFinish (
	BeginningField varchar(20),
	MiddleField varchar(20),
	EndField varchar(20)
)


DECLARE @BeginningField varchar(20),
	@MiddleField varchar(20),
	@EndField varchar(20)

DECLARE CursorWork SCROLL CURSOR FOR
	SELECT BeginningField, MiddleField, EndField
	FROM #TempTable
	ORDER BY EndField
   FOR READ ONLY
     
OPEN CursorWork

FETCH NEXT FROM CursorWork INTO @BeginningField, @MiddleField, @EndField
WHILE (@@FETCH_STATUS) <> -1
BEGIN
   IF (@@FETCH_STATUS) <> -2
   BEGIN
	IF EXISTS (SELECT * FROM #TempFinish WHERE BeginningField = @BeginningField AND MiddleField = @MiddleField)
	BEGIN
		UPDATE #TempFinish 
		SET EndField = EndField + ',' + @EndField
		WHERE BeginningField = @BeginningField 
		AND MiddleField = @MiddleField
	END
	ELSE
	BEGIN
		INSERT INTO #TempFinish
		SELECT @BeginningField, @MiddleField, @EndField
	END
   END
   FETCH NEXT FROM CursorWork INTO @BeginningField, @MiddleField, @EndField
END

/*   Clean up   */
CLOSE CursorWork
DEALLOCATE CursorWork

>Hi,
>
>I want to transform some records from
>
>x name a
>x name b
>x name c
>y nme2 a
>y nme2 b
>
>to
>
>x name a,b,c
>y nme2 a,b
>
>Is there a way to use SQL to do this?