SQL: How to concatenate every group of N rows into a single row

Question

I have this

 ID | Name 
----+-------
 31 | Abby
 24 | Bruce 
 44 | Carl 
 49 | Derek
 55 | Eric
 81 | Fred

I want to concatenate groups of N rows into a single row. For N = 3, this would give me this

    ID    |    Name 
----------+----------------
 31,24,44 | Abby,Bruce,Carl
 49,55,81 | Derek,Eric,Fred

I managed to generate a row to use GROUP BY and CONCAT on, but it only works in mysql...

SET @row_number = 0;
SELECT *, (@row_number:=@row_number + 1) AS r1, (@row_number - 1) DIV 3 AS r2 FROM table1

 ID | Name  | r1| r2
----+-------+---+---
 31 | Abby  | 1 | 0
 24 | Bruce | 2 | 0
 44 | Carl  | 3 | 0
 49 | Derek | 4 | 1
 55 | Eric  | 5 | 1
 81 | Fred  | 6 | 1

For clarification:

• I want a vanilla-like SQL solution (So it will work in mysql, sybase, oracle and postgres)

• I don't need any order, I just want to reconstitute the original table at some point

• I don't have writing privileges on this base, only reading

• I want to concatenate any columns type (by casting them to a string) and handle NULLs

• It's ok if some groups are not exactly of size N (like the last one)

Answer 1

Amazing script. But I suspect you left out an AS. So I made it like so:

select string_agg(t.[id], ',') as ids, string_agg(t.[name], ',') as names from

(select t.*, row_number() over (order by id) as seqnum from [tablename] ) AS t

group by cast( (seqnum - 1) / 3 as int);

In my case, it is as such (though I could not get the 'within group (order by id)' to work at any way..... hmmmm)

Here is mine that works well, which is a list of emails to all my students, combined into a row for every 100 rows. The String_Agg limits it to 8000 character, sadly. Anyone knows any alternative to String_Agg for an SQL Server?

SELECT string_agg(t.[Student Name], ';') as [All Names], string_agg(t.[Student Email], ';') as [All Emails]

FROM ( SELECT [Student Name], [Student Email], ROW_NUMBER() OVER (ORDER BY [Student Email]) AS RowNo FROM [Mailing List For Courses] where [Prod Name]='Online Courses' ) AS t group by cast( (RowNo - 1) / 100 as int);

Hope it helps <3

Answer 2

The standard SQL solution looks something like this:

select listagg(id, ',') within group (order by id) as ids,
       listagg(name, ',') within group (order by id) as names
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t
group by cast( (seqnum - 1) / 3 as int);

I think this will work as-is in Oracle. In MySQL, you need to change listagg() to group_concat() (and be using MySQL 8+) and in Postgres, you need to change listagg() to string_agg().

And, you pretty much can't do this easily in Sybase.

Oh, wait, there is another way:

select concat( (case when seqnum % 3 = 1 then concat(id, ';') else '' end),
               (case when seqnum % 3 = 2 then concat(id, ';') else '' end),
               (case when seqnum % 3 = 0 then concat(id, ';') else '' end)
             ) as ids,
       concat( (case when seqnum % 3 = 1 then concat(name, ';') else '' end),
               (case when seqnum % 3 = 2 then concat(name, ';') else '' end),
               (case when seqnum % 3 = 0 then concat(name, ';') else '' end)
             ) as name           
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t
group by cast( (seqnum - 1) / 3 as int);

Of course, Sybase doesn't support concat(), so you have to use +. And this produces ; for the separator rather than ,, but it is pretty close.