combining rows into a single row output

[FuseICT]

Hi,
for the purpose of this question i have 2 tables:

Table1 with fields ID, Name, NoteID
Table2 with fields NoteID, Note_Text

for every 1 record in Table1 there are many in Table2. If i use a standard JOIN i get multiple rows in a recordset but what i want is 1 Row with the field "Notes" that would comtain all the Note_text entries for the record in Table1.

example:
ID, Name, Notes
------------------------
1, Bob, Test note1, Test note2, Test note3
2, Carl, <null>
3, paul, note for paul

can anyone help please?

[rharvieuxsql]

-- Try this:

SET NOCOUNT ON

DECLARE @ID varchar (50)
DECLARE @Name varchar (200)
DECLARE @StringText varchar(8000)


Declare curBuildString cursor
static for
select distinct id, Name from Table1

OPEN curBuildString
FETCH NEXT FROM curBuildString
INTO @ID, @Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @StringText = @id + ', ' + rtrim(@name) +', '
SELECT Table2.notetext into #t1
FROM Table1
INNER JOIN Table2 ON Table1.noteid = Table2.noteid
WHERE Table1.ID = @ID

WHILE (Select top 1 notetext from #t1) is not null
BEGIN
select @StringText = @StringText + rtrim((select top 1* from #t1)) +', '
DELETE FROM #t1 WHERE #t1.notetext = (SELECT TOP 1 notetext FROM #t1)
END

print left(@StringText, len(@StringText) -1)
DROP TABLE #t1
FETCH NEXT FROM curBuildString
INTO @ID, @Name
END

CLOSE curBuildString
DEALLOCATE curBuildString
SET NOCOUNT OFF
GO