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

Question

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

Answer 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