Join Multiple Rows Into One Column Using A Function In Oracle With 3 Related Tables

I know there are multiple questions about this same question but I couldn't find one that would fix my problem. I also know about this article and that's exactly what I used for th

Solution 1:

You need to change your function to resemble:

CREATE OR REPLACE FUNCTION f_test(IN_ID IN TABLE_C.a_id%TYPE) 
RETURN VARCHAR2 
IS

  l_text  VARCHAR2(32767) := NULL;

BEGIN

  FOR cur_rec IN (SELECT b.b_name 
                    FROM TABLE_B b
                    JOIN TABLE_C c ON c.b_id = b.b_id
                   WHERE c.a_id = IN_ID) LOOP
    l_text := l_text || ',' || cur_rec.b_id;ENDLOOP;

  RETURN LTRIM(l_text, ',');END;

Then, you can use:

SELECT a.a_name, 
       f_test(a.a_id)
  FROM TABLE_A a

Solution 2:

change the SQL Query to get the list of bnames instead of b_ids

from

SELECT b_id FROM table_c WHERE a_id = id

to

SELECT b_name 
  FROM table_b b, table_c c 
   WHERE b.b_id = c.c_id
     and b.a_id = id

Instead of getting the IDs from table b, you should instead be getting the names.

Solution 3:

Firstly, as @OMGPonies points out, you need to rewrite the function so it retrieves the required data from TABLE_B.

SQL>createor replace function f_test
  2      (p_id in table_c.a_id%type)
  3return varchar2
  4is5      l_text  varchar2(32767) :=null;
  6begin7for cur_rec in (select b_name
  8from table_c c
  9join table_b b
 10on (b.b_id = c.b_id)
 11where c.a_id = p_id)
 12      loop
 13          l_text := l_text ||','|| cur_rec.b_name;
 14end loop;
 15return ltrim(l_text, ',');
 16end;
 17/Function created.

SQL>

Likewise you need to join to TABLE_A in the query:

SQL> select a.a_name
  2          , f_test(c.a_id)
  3from table_c c
  4join table_a a
  5on (a.a_id = c.a_id)
  6groupby a.a_name
  7  /
        , f_test(c.a_id)
                 *
ERROR at line 2:
ORA-00979: not a GROUPBY expression


SQL>

Oh, that was a surprise. The point is, F_TEST is not a aggregating function, so GROUP BY doesn't work here unless we include the function as well:

SQL>select a.a_name
  2          , f_test(c.a_id) 
  3from table_c c
  4join table_a a
  5on (a.a_id = c.a_id)
  6groupby a.a_name, f_test(c.a_id)
  7/

A_N F_TEST(C.A_ID)
--- ---------------------------------------------
bbb ddd
ccc ddd,eee,fff
aaa eee,fff

SQL>

Incidentally, without the GROUP BY clause we would get six rows (one for each row in TABLE_C). Which we don't want.

The problem with the solution is one of performance: the function is executed once for each row in TABLE_C. That loop on TABLE_B would get a bit expensive if either table were large. The aggregating solution is more efficient:

SQL> select a.a_name
  2          , wm_concat(b.b_name) 
  3from table_c c
  4join table_b b
  5on (b.b_id = c.b_id)
  6join table_a a
  7on (a.a_id = c.a_id)
  8groupby a.a_name
  9  /

A_N WM_CONCAT(B.B_NAME)
--- ---------------------------------------------
aaa fff,eee
bbb ddd
ccc fff,eee,ddd

SQL>