Single SQL Select for one-to-many relationship tables using delimiter in column - Oracle

Question

I have two tables with one-to-many relationship.

Table Person:

Id_person    Name
-----------------------
1            Jack Black
2            Joe White

Table Telephone:

Id_telephone Id_person Number     Type
----------------------------------------
5            1         333222111  Mobile
6            1         444333222  Fax
7            2         555444333  Mobile

Desired result of SQL SELECT statement:

Name        Telephone_Numbers
---------------------------------------------
Jack Black  Mobile: 333222111, Fax: 444333222
Joe White   Mobile: 555444333

I found many answers to this question, but related to SQL Server, i have no idea how to accomplish this with Oracle Database 10g. I need to populate Gridview in ASP.NET with this result, so i want to use this SELECT statement with OracleCommand and ExecuteQuery().

Thanks for any hints.

score 2 · Accepted Answer · edited May 23 '17 at 09:58

in 11gR2 you may use listAgg

with person as
(
select 1   id_person, 'Jack Black' FullName from dual
union all
select 2   id_person, 'Joe White' FullName  from dual
)
, telephone as
(
select 5  id_telephone ,          1    Id_person,     333222111 phone_Number ,  'Mobile' phone_type from dual
union all
select 6  id_telephone ,          1    Id_person,     444333222 phone_Number ,  'Fax' phone_type from dual
union all
select 7  id_telephone ,          2    Id_person,     555444333 phone_Number ,  'Mobile' phone_type from dual
)
select person.FullName  
      ,listagg(telephone.phone_type || ': ' || telephone.phone_number , ', ') within group (order by person.FullName ) personToPhone

  from person 
       inner join
       telephone
         on person.id_person = telephone.id_person 
group by person.FullName ;


FULLNAME   PERSONTOPHONE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- -----------------------------------
Jack Black Fax: 444333222, Mobile: 333222111                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Joe White  Mobile: 555444333

Look at this question for other approaches: comma-separated list as a result of select statement in Oracle

EDIT

for 10g you may use XMLAgg

with person as
(
select 1   id_person, 'Jack Black' FullName from dual
union all
select 2   id_person, 'Joe White' FullName  from dual
)
, telephone as
(
select 5  id_telephone ,          1    Id_person,     333222111 phone_Number ,  'Mobile' phone_type from dual
union all
select 6  id_telephone ,          1    Id_person,     444333222 phone_Number ,  'Fax' phone_type from dual
union all
select 7  id_telephone ,          2    Id_person,     555444333 phone_Number ,  'Mobile' phone_type from dual
)
select person.FullName  
    ,RTRIM(XMLAGG(XMLELEMENT(e,telephone.phone_type || ': ' || telephone.phone_number || ',')).EXTRACT('//text()'),',') AS TelephoneToType
  from person 
       inner join
       telephone
         on person.id_person = telephone.id_person 
group by person.FullName ;

FULLNAME   TELEPHONETOTYPE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
---------- ---------------------------------
Jack Black Mobile: 333222111,Fax: 444333222                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
Joe White  Mobile: 555444333

(as Mark Baker did in the linked SO question)

@sanjuro added a different approach above – Harrison Mar 17 '11 at 19:47 — Harrison, Mar 17 '11 at 19:47

score 1 · Answer 2 · answered Mar 17 '11 at 19:38

Select P.Name
    , 'Mobile: ' || Coalesce( Min( Case When T.Type = 'Mobile' Then Number End ),'' )
        + Coalesce( 'Fax: ' || Min( Case When T.Type = 'Fax' Then Number End ),'' )
From Person P
    Join Telephone T
        On T.Id_Person = P.Id_person
Group By P.Name

score 1 · Answer 3 · answered Mar 17 '11 at 19:39

1

There is a list of possible solutions here

answered Mar 17 '11 at 19:39

Johann Blais

9,389
6
45
65

joe · Answer 4 · 2011-03-24T21:45:01.910

Had the same problem awhile back. All of the answers that I found said to use listagg, but client uses 10g. Create this function. Function is not mine found online.

create or replace
FUNCTION column_concat
(
    p_cursor sys_refcursor,
    p_del VARCHAR2 
)  RETURN VARCHAR2
IS
    l_value   VARCHAR2(32767);
    l_result  VARCHAR2(32767);
BEGIN
    LOOP
        FETCH p_cursor INTO l_value;
        EXIT WHEN p_cursor%notfound;
        IF l_result IS NOT NULL THEN
        l_result := l_result || p_del;
    END IF;
    l_result := l_result || l_value;
END LOOP;
RETURN l_result;
END column_concat;

use it in Select like so:

select pers.name Names, column_concat(cursor(SELECT tel.type ||':' ||  tel.telnumber  FROM person per, telephone tel Where per.id_person = tel.id_person and tel.id_person = pers.id_person), ',' ) Telephone_Numbers from person pers;

score 0 · Answer 5 · answered Mar 17 '11 at 19:42

This should work for you. If you are on 11g, there is a built in aggregate function LISTAGG which is easier:

with data
as
(  
    select
        p.id_person,
        p.name,
        t.type||': '||t.phone_number telephone_number,
        row_number() over (partition by p.id_person order by t.type) rn,
        count(*) over (partition by p.id_person) cnt
    from
        person p
        inner join telephone t
            on t.id_person = p.id_person 
)
select
    name,
    ltrim(sys_connect_by_path(telephone_number,', '),', ') telephone_number
from data
where rn = cnt
start with rn = 1
connect by prior id_person = id_person and prior rn = rn-1
order by id_person

Single SQL Select for one-to-many relationship tables using delimiter in column - Oracle

5 Answers5