how to split multiple rows in the same column into different columns depending on the data provided postgres

All we need is an easy explanation of the problem, so here it is.

so this is what I have…

id name children children’s age
1 Rachel john 20
1 Rachel cell 10
1 Rachel jay 15
2 Jereme Les 17
2 Jereme greg 5.5
2 Jereme ven 27

but I’m trying to find a way to get this kind of results……

id name child1 age child2 age child3 age
1 Rachel john 20 cell 10 jay 15
2 Jereme Les 17 greg 5.5 ven 27

so if I can get any help in the query part…. I’m still a beginner in psql so if you can explain as well… thank you

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

A standard conditional aggregation (pivot) query should work

SELECT
  t.id,
  t.name,
  MAX(t.children) FILTER (WHERE t.rn = 1) AS child1,
  MAX(t.age)      FILTER (WHERE t.rn = 1) AS age1,
  MAX(t.children) FILTER (WHERE t.rn = 2) AS child2,
  MAX(t.age)      FILTER (WHERE t.rn = 2) AS age2,
  MAX(t.children) FILTER (WHERE t.rn = 3) AS child3,
  MAX(t.age)      FILTER (WHERE t.rn = 3) AS age3
FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY t.id, t.name ORDER BY t.children) AS rn
    FROM YourTable t
) t
GROUP BY
  t.id,
  t.name;

db<>fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply