Get multiple data into one colomn Oracle Query

Question

I have 3 tables: table_A, table_B and table_C. Table_A has a Primary key and is referred by a foreign key from table_B. Table_C has a primary key referred by a foreign key from table_B. The design is like this:

Table_A:
ID_A
TextData

Table_B:
ID_B
ID_A
ID_C

Table C:
ID_C
TextData

I want to join between 3 tables like this:

select A.ID_A, A.TextData as DataA, 
(
  select C.TextData
  from Table_B B, Table_C C 
  where B.ID_C = C.ID_C and B.ID_A = C.ID_A
) as Data_C
from Table_A;

I know that it should be an error if I try to compile it with error like: return more than one elements.

But my client want me to join all data from table C into one row, all i know using concate to every data. But i don't know how to do it. I never try to create function or package on oracle. Can you help me how to fix my query?

The result should be like:

ID_A    | DataA     | Data_C
1         texta       text1, text2, text8
2         textb       text2, text3, text9
3         textc       text1, text8, text9

Answer 1

You can try with this:

SETUP

create table table_A(ID_A number, TextData varchar2(100))
/
create table Table_B( ID_B number, ID_A number, ID_C number)
/
create table Table_C(ID_C number, TextData varchar2(100))
/
insert into table_A values (1, 'texta');
insert into table_A values (2, 'textb'); 
insert into table_A values (3, 'textc');
--
insert into table_C values (1, 'text1');
insert into table_C values (2, 'text2');
insert into table_C values (3, 'text3');
insert into table_C values (8, 'text8');
insert into table_C values (9, 'text9');
--
insert into table_b values (11, 1, 1);
insert into table_b values (12, 1, 2);
insert into table_b values (18, 1, 8);
insert into table_b values (22, 2, 2);
insert into table_b values (23, 2, 3);
insert into table_b values (29, 2, 9);
insert into table_b values (31, 3, 1);
insert into table_b values (38, 3, 8);
insert into table_b values (39, 3, 8);

QUERY:

select id_a, a.textData as DataA, listagg(c.textData, ', ') within group (order by c.textData) as Data_c
from table_A a
  inner join table_B b
   using(id_A)
  inner join table_c c
   using(id_c)
group by id_a, a.textData

Answer 2

XMLAGG or similar is what you need. Something like (not tested, but should give you a hint):

select A.ID_A, A.TextData as DataA, 
(
  select XMLELEMENT("Thedata",XMLAGG("textdata",C.TextData)) as td
  from Table_B B, Table_C C 
  where B.ID_C = C.ID_C and B.ID_A = C.ID_A
) as Data_C
from Table_A; 

Use REPLACE/TRANSLATE/REGEXP_REPLACE etc to strip out the XML tags if required.

Loads of example about e.g. http://www.dba-oracle.com/t_converting_rows_columns.htm .

Answer 3

May be you should write your aggregate. Funcrion to concat your strings user defined aggregates

Or use undocumented wm_concat. Thus function deprecated in 12c.

Answer 4

If you're on Oracle 11g or higher, you can use LISTAGG:

with table_a as (select 1 id_a, 'texta' textdata from dual union all
                 select 2 id_a, 'textb' textdata from dual union all
                 select 3 id_a, 'textc' textdata from dual),
     table_b as (select 1 id_b, 1 id_a, 1 id_c from dual union all
                 select 2 id_b, 1 id_a, 2 id_c from dual union all
                 select 3 id_b, 1 id_a, 4 id_c from dual union all
                 select 4 id_b, 2 id_a, 2 id_c from dual union all
                 select 5 id_b, 2 id_a, 3 id_c from dual union all
                 select 6 id_b, 2 id_a, 5 id_c from dual union all
                 select 7 id_b, 3 id_a, 1 id_c from dual union all
                 select 8 id_b, 3 id_a, 4 id_c from dual union all
                 select 9 id_b, 3 id_a, 5 id_c from dual),
     table_c as (select 1 id_c, 'text1' textdata from dual union all
                 select 2 id_c, 'text2' textdata from dual union all
                 select 3 id_c, 'text3' textdata from dual union all
                 select 4 id_c, 'text8' textdata from dual union all
                 select 5 id_c, 'text9' textdata from dual)
-- end of mimicking your tables; see sql below.
select a.id_a,
       a.textdata,
       listagg(c.textdata, ', ') within group (order by c.id_c) data_c
from   table_a a
       inner join table_b b on (a.id_a = b.id_a)
       inner join table_c c on (b.id_c = c.id_c)
group by a.id_a, a.textdata;

      ID_A TEXTDATA DATA_C              
---------- -------- --------------------
         1 texta    text1, text2, text8 
         2 textb    text2, text3, text9 
         3 textc    text1, text8, text9