Add a group of concatenated rows from a table to another table. Script should work under SQL Server, Oracle, PostgreSQL

Question

If I have a group of columns in a table like TEMP_TABLE (id int, text VARCHAR(max)); and I want to add all the records of this table in another table, concatenated, what would be the best solution, considering that this script should work also for SQL Server, Oracle, PostgrSQL.

So the records are like:

insert into TEMP_TABLE(1, 'aaa');
insert into TEMP_TABLE(2, 'bbb');
insert into TEMP_TABLE(3, 'ccc');
...
insert into TEMP_TABLE(10, 'zzz');

and the result should look like

insert into DEST_TABLE (key,name,value) values (1,'concatenated strings','aaabbbccc...zzz')

Thanks!

Answer 1

There is no way to do this that is compatible with all the databases. because your description is specific to SQL Server (varchar(max)), you cannot even create the table in any other database. The solution in that database is:

select string_agg(text, '') within group (order by id)
from temp_table;

Postgres uses string_agg() as well, but the syntax would be:

select string_agg(text, '' order by id desc)

And in Oracle:

select listagg(text, '') within group (order by id)

Well, actually there is a portable way if you know the maximum number of values:

select concat(case when seqnum = 1 then text end,
              concat(case when seqnum = 2 then text end,
                     concat(case when seqnum = 3 then text,
                            . . .
                           )
                    )
             )
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t;

Unfortunately, concat() in Oracle only takes two arguments so you have to keep nesting them.