Transposing rows to columns in oracle

user_13102609 0
Viewed 69

I'm trying to transpose rows in an Oracle statement into columns. I've been trying to read up and I guess what I need is a PIVOT. But from countless examples I'm not able to figure out what I need to write.

I have two tables, one with persons and with with relations. Parents have one reference each to a child, so two parents will be two rows in the relation table.

Person table:

id        name
1         John Doe
2         Jane Doe
3         Johnny Doe

Relation table:

person_1_id    person_2_id
1              3
2              3

So If I run the following SQL:

SELECT child.id AS child_id, r.person_1_id AS parent_id
    FROM person child
    JOIN relation r ON r.person_2_id = child.id;

I get the following output:

child_id    parent_id  
3           1  
3           2

How would I go about getting the output as:

child_id   parent_1_id   parent_2_id
3          1             2

I'm running Oracle 12c.

oracle sql
2 Answers

You can achieve the desired result using conditional aggregation as follows:

SELECT
    CHILD_ID,
    MAX(CASE WHEN RN = 1 THEN PARENT_ID END) AS PARENT_1_ID,
    MAX(CASE WHEN RN = 2 THEN PARENT_ID END) AS PARENT_2_ID
FROM
    ( SELECT
          CHILD.ID        AS CHILD_ID,
          R.PERSON_1_ID   AS PARENT_ID,
          ROW_NUMBER() OVER(PARTITION BY CHILD.ID ORDER BY R.PERSON_1_ID) AS RN
        FROM
          PERSON CHILD
          JOIN RELATION R ON R.PERSON_2_ID = CHILD.ID
    )
GROUP BY CHILD_ID;
user_115656290

You can just use aggregation:

SELECT child.id AS child_id,
       MIN(r.person_1_id) AS parent_id_1,
       NULLIF(MAX(r.person_1_id), MIN(r.person_1_id) AS parent_id_2
FROM person child JOIN
     relation r
     ON r.person_2_id = child.id
GROUP BY child.id;

Subqueries do not seem to be necessary for this.

user_11440350
Related