Database Administration
postgresql pivot
Updated Mon, 05 Sep 2022 00:13:14 GMT

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


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



Solution

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





Comments (5)

  • +0 – Is it possible to add a condition? For example if there was an additional row like this... 3 | Larry | Grey | 12 ...... so if Larry is called, then it'll just show the columns "child1" and "age" ignoring the rest.... I don't know if something like that is possible? — Jul 07, 2022 at 10:34  
  • +0 – Not sure what you mean. my query would get this dbfiddle.uk/… is that not what you want? If you mean that you want the number of columns to be dynamic, I would advise you not to go down that route, it's very messy — Jul 07, 2022 at 10:36  
  • +0 – Not really....I'm using metabase , so it displays as a question(not showing the query part just the results, and based on the filters used).... so I want it like based on the info wanted, it'd either display or not.... I don't know if you get me now? — Jul 07, 2022 at 10:40  
  • +0 – so let's add another row....4 | Jer | white | 16...... if I want to just call on Larry and Jer, it should show the results for id, name, child1 and age....... So like there's no need to change the info in the query, but just on the filters used...(let's make id, and name as the filters) — Jul 07, 2022 at 10:42  
  • +0 – No I don't get you at all. Perhaps you just want a WHERE filter? dbfiddle.uk/… I think you might need to make a new question for this — Jul 07, 2022 at 11:51  


External Links

External links referenced by this document: