Combine Multiple Rows Into One "memo" Nvarchar(max)

2 tables 1 with clients other with accno information table 1: file, accno <- nvarchar(50) t1, 123a t1, 456a t1, 789a t2, 012b t3, 345c t3, 678c i would like to transfer table 1

Solution 1:

SELECTDISTINCT [File]
    ,STUFF(
        (SELECT','+ accno
        FROM
            TableName it
        WHERE it.[File] = ot.[File]
        FOR XML PATH(''))

        ,1,1,'') AS [Memo]
FROM
    TableName ot

You can adapt this but this will create a comma delimited list of your accno which I think is kind of what you are looking for? You can modify to do it with line endings in your Memo Field rather than commas.

Solution 2:

k so it turns out that the funny gizmozs on the ends was carriage returns, i managed to fix that by modifying query with the below

SELECTDISTINCT fileref, stuff(
        (
        Select'; '+ IsNull( D2.viaccno, '' )
        From vi_accno As D2
        Where D2.vifileref = A.FileRef
        For Xml Path(''), type
        ).value('.', 'nvarchar(max)')
        , 1, 2, '') As SelectedComments
FROM
   vtindex a

Thanks to @Matt for the help and response