combining rows into a single row output

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • FuseICT
    New Member
    • Apr 2007
    • 1

    #1

    combining rows into a single row output

    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
    New Member
    • Nov 2006
    • 9

    #2
    -- 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(@StringTex t, len(@StringText ) -1)
    DROP TABLE #t1
    FETCH NEXT FROM curBuildString
    INTO @ID, @Name
    END

    CLOSE curBuildString
    DEALLOCATE curBuildString
    SET NOCOUNT OFF
    GO

    Comment

    Working...