Troz Troz - 1 year ago 84
SQL Question

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 to table 2

table 2:
file, accno <- nvarchar(max)
t1, 123a
t2, 012b
t3, 345c

i can do this is delphi very easy, but itll take forever almost 6 hrs because im filtering close to 300000 records on table 1

is there an sql query that can make this go super fast, even if it takes 5min

Answer Source
    DISTINCT [File]
        (SELECT ',' + accno
            TableName it
        WHERE it.[File] = ot.[File]
        FOR XML PATH(''))

        ,1,1,'') AS [Memo]
    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.