Concatenating PostgreSQL records by column, with max size

technoob 0
Viewed 13

How do I concatenate below's records such that they would be concatenated a maximum of 3 records?

Column A Column B
Numbers One
Numbers Two
Numbers Three
Alphabets A
Alphabets B
Alphabets C
Alphabets D

Will return:

Column A Column B
Numbers One, Two, Three
Alphabets A, B, C
Alphabets D

Thanks in advance.

postgresql
1 Answers
WITH tableA as (
    SELECT tbl.columnA, tbl.columnB, CAST(row_number() OVER (PARTITION BY columnA ORDER BY columnB) as INT) AS row_number
    FROM tbl
    ORDER BY columnA
)
SELECT tableA.columnA, string_agg(DISTINCT columnB, ',  ') as columnB 
FROM tableA
GROUP BY tableA.columnA, row_number/5
technoob0
Related