Question 178868 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgresql concat n rows together in a select?

So i have a table that contains two columns id_readings (Auto_Inc) and name like so

id_readings name
1 jim
2 jon
3 jun
4 esd
5 job
6 jif
9 jef
11 pol
12 pil
16 jml
19 cid

So if i want to concat every 3 rows together, regardless of the value id_readings or name, it should look like this

concat
jim,jon,jun
esd,job,jif
jef,pol,pil
jml,cid

Thanks for the help in advance

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So for those want to know here's how i solved it, with the help of @Bergi answer :

    SELECT string_agg(name, ',')
FROM (
   SELECT name, ROW_NUMBER() over(ORDER BY id_readings) / 3 AS group_id
   FROM data
) AS test
GROUP BY group_id

    Thanks again to him


  2. 0

    The following query will produce the requested results:

    WITH t AS (SELECT (ROW_NUMBER() OVER (PARTITION BY NULL) - 1) / 3 AS group_id, name
             FROM some_table)
SELECT STRING_AGG(name, ', ')
  FROM t
 GROUP BY group_id;

    The ordering of the names is not specified. All but at most one group will have three names.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search