Merging multiple rows into one using Postgresql

Question

I am trying to combine multiple rows with the same IDs to one.

My raw table looks like this:

ID | person_id | eur_amount
1        3           200
1        2           100
2        3           80
2        2           100

The output should look like this:

ID | person_1 | eur_amount_1 | person_2 | eur_amount_2 |
1        3           200           2           100
2        3            80           2           100

The max number of persons is the same. I already tried solving it with a multiple JOIN statements and the crosstab() function as mentioned here PostgreSQL Crosstab Query.

But I couldn't find a solution for this - does anyone know a good way to achive the desired output?

Thanks in advance!

My first question would be why would you want to do this? But I'll digress...is person_id already known in advance (IE the query only ever looks for personID 2 and 3) and is two persons the upper limit of columns or will you have more? This seems like you're trying to get SQL to do something the reporting GUI should be worrying about instead. Should also ask what determines which value goes into person_1 vs person_2, is that just the order of the rows going in? — Twelfth, Jul 22 '16 at 15:04
`select id, array_agg(row(person_id, eur_amount) order by person_id desc) as perdata from my_raw_rable group by id;` — Abelisto, Jul 22 '16 at 15:53

score 0 · Answer 1 · answered Jul 22 '16 at 15:32

You can do this using cross tab or conditional aggregation. But you need to identify the rows, using row_number(). For instance:

select id,
       max(case when seqnum = 1 then person_id end) as person_id_1,
       max(case when seqnum = 1 then eur_amount end) as eur_amount_1,
       max(case when seqnum = 2 then person_id end) as person_id_2,
       max(case when seqnum = 2 then eur_amount end) as eur_amount_2
from (select t.*,
             row_number() over (partition by id order by id) as seqnum
      from t
     ) t
group by id;

Merging multiple rows into one using Postgresql

1 Answers1