Search code examples
sqlpostgresqlgroup-bygroup-concat

How do I group concat each row?

I have this query 

SELECT 1 as row, array_agg(id) as ids FROM users GROUP BY row;

Results

  row  |   ids
---------------
  1     {1,2,3,4}

I have a lot more results that can't fit in one row...so I'm trying to build a query that does this:

  row  |   ids
---------------
  1     {1,3,4,5}
  2     {4,6,9,20}
  3     {21,24,26,30}
  etc.....

I want to limit the ids to say 50 per row with as many rows as possible. How can I accomplish this?

Solution

  • row_number() over() returns the row number. Then just do a subquery to group them together. Change 4 to the number you actually want

    select a.r, array_agg(a.id) ids
from (
  select (row_number() over()-1)/4 r, id
  from users) a
group by a.r;

    demo: http://sqlfiddle.com/#!15/5c738/22