How to select an inner join with multiple rows?

If you are using a version of Oracle 11G or later, you can use the PIVOT statement. Here's an example of how it works.

WITH CARACTERISTICA_PERSONA AS (
     SELECT 1 ID_PERSONA, 'Nombre' CARACTERISTICA, 'Pedro' VALOR FROM DUAL UNION ALL
     SELECT 1, 'Apellido', 'Perez' FROM DUAL UNION ALL
     SELECT 1, 'Edad', '33' FROM DUAL UNION ALL
     SELECT 2, 'Nombre', 'Camila' FROM DUAL UNION ALL
     SELECT 2, 'Apellido', 'Páez' FROM DUAL UNION ALL
     SELECT 2, 'Edad', '12' FROM DUAL UNION ALL
     SELECT 3, 'Nombre', 'Esteban' FROM DUAL UNION ALL
     SELECT 3, 'Apellido', 'Abondano' FROM DUAL UNION ALL
     SELECT 3, 'Edad', '20' FROM DUAL)
SELECT *
FROM CARACTERISTICA_PERSONA CP
PIVOT (MAX(VALOR) AS VALOR FOR (CARACTERISTICA)  IN ('Nombre', 'Apellido', 'Edad'))

Focus on the part of the last SELECT , the WITH is just to simulate a table. Here is the result of the previous query:

In any case, I also leave you a link to a complete example of the PIVOT statement. In this same link you will find an alternative solution to PIVOT for Oracle versions prior to 11G.

link

I edit my answer adding a solution for Oracle 10.

 WITH CARACTERISTICA_PERSONA AS (
         SELECT 1 ID_PERSONA, 'Nombre' CARACTERISTICA, 'Pedro' VALOR FROM DUAL UNION ALL
         SELECT 1, 'Apellido', 'Perez' FROM DUAL UNION ALL
         SELECT 1, 'Edad', '33' FROM DUAL UNION ALL
         SELECT 2, 'Nombre', 'Camila' FROM DUAL UNION ALL
         SELECT 2, 'Apellido', 'Páez' FROM DUAL UNION ALL
         SELECT 2, 'Edad', '12' FROM DUAL UNION ALL
         SELECT 3, 'Nombre', 'Esteban' FROM DUAL UNION ALL
         SELECT 3, 'Apellido', 'Abondano' FROM DUAL UNION ALL
         SELECT 3, 'Edad', '20' FROM DUAL)
    SELECT CP.ID_PERSONA,
           MAX(DECODE(CP.CARACTERISTICA, 'Nombre', CP.VALOR)) AS NOMBRE,
           MAX(DECODE(CP.CARACTERISTICA, 'Apellido', CP.VALOR)) AS APELLIDO,
           MAX(DECODE(CP.CARACTERISTICA, 'Edad', CP.VALOR)) AS EDAD
    FROM CARACTERISTICA_PERSONA CP
    GROUP BY CP.ID_PERSONA
    ORDER BY CP.ID_PERSONA

Greetings

Look, a solution although very antiperformante is pivoting several times the same table T3:

SELECT t3_1.valor,
       t3_2.valor,
       t3_3.valor,
       t1.*
  FROM tabla1 t1                    INNER JOIN
       tabla2 t2   ON t1.id=t2.id   INNER JOIN
       tabla3 t3_1 ON t2.id=t3_1.id INNER JOIN
       tabla3 t3_2 ON t2.id=t3_2.id INNER JOIN
       tabla3 t3_3 ON t2.id=t3_3.id INNER JOIN
 WHERE t3_1.valor <> t3_2.valor
   AND t3_3.valor <> t3_2.valor
   AND t3_3.valor <> t3_1.valor;

But this would only be valid if we have 3 fixed values for each record of T1.

Another way is by using an aggregation function:

SELECT LISTAGG(t3.valor, ' ') WITHIN GROUP (ORDER BY t3.id)
       t1.*
  FROM tabla1 t1                INNER JOIN
       tabla2 t2 ON t1.id=t2.id INNER JOIN
       tabla3 t3 ON t2.id=t3.id
 GROUP BY t1.id;

But then you would have a solocmpo that would be the concatenation of others.

Personally I think the best way to get the dataset you want is to use the query that you already have written and then convert the result using a control cut.