1

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

6
  • You may find a related answer to this question here dba.stackexchange.com/a/311054/152218 However, what I see from your example is that you're converting unique users to columns which makes little logical sense since in this case a single row represents multiple users with no unique denominator. You might want to clarify your question, include real data and definition of your tables etc.
    – Chessbrain
    Jul 6, 2022 at 8:45
  • @Chessbrain crosstab doesn't work with what I want.... ids 1 and 2 are the unique ids, and the names in column a are subsets of the column id(they belong to), with the values in column b which belong to the names in a......I don't know if you get me now?
    – w123
    Jul 6, 2022 at 9:00
  • Is there a reason you want to achieve this? You usually try to avoid this exact thing. There will probably not be a simple SQL to do this, since you try to convert a table which could contain any amount of rows into a pivot with rows for three children. Honestly, I'd probably do it in another programming language, but you have a lot of edge cases (what if there are more than 3 children? what if there are less?) and querying this table is a nightmare (what if child1 is empty, but there is a value in child2?). You might be better off using datatypes like arrays or json.
    – Flourid
    Jul 6, 2022 at 14:37
  • So why doesn't cross-tab/pivot work? dbfiddle.uk/… Jul 6, 2022 at 14:39
  • @FlouridFlour it was what was requested of me to do, so I have been searching for ways to do so if it was possible....
    – w123
    Jul 7, 2022 at 8:17

1 Answer 1

2

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

8
  • 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?
    – w123
    Jul 7, 2022 at 10:34
  • 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 7, 2022 at 10:36
  • 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?
    – w123
    Jul 7, 2022 at 10:40
  • 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)
    – w123
    Jul 7, 2022 at 10:42
  • 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 7, 2022 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.