Oracle SQL query select one to many, mult row into one row result

Question

I am learning sql and trying to figure out how to write the query for two table in a one to many relationship.

Table1 has the person information : (PersonId, FirstName, MiddleName, LastName) and table2 has (PersonId, Phone, PhoneType).

Here is my query so far

select Table1.PERSON_ID, 
Table1.FIRST_NAME, 
Table1.MIDDLE_NAME, 
Table1.LAST_NAME, 
Table2.PHONE_NUMBER
from Table1
inner join Table2
on Table2.PERSON_ID = Table1.PERSON_ID
where Table2.PHONE_TYPE in ('BUSINESS','PERSONAL','HOME')

Here is a tables

Table1
PERSON_ID     FIRST_NAME     MIDDLE_NAME     LAST_NAME
1             John           Carter          Jones

Table2
PERSON_ID     PHONE_NUMBER   PHONE_TYPE
1             111-111-1111   HOME
1             111-111-1112   PERSONAL
1             111-111-1113   BUSINESS

From my query I get

1     John Carter Jones 111-111-1111
1     John Carter Jones 111-111-1112
1     John Carter Jones 111-111-1113

I would like to achieve the following result

1     John Carter Jones 111-111-1111 111-111-1112 111-111-1113

Any help is greatly appreciated.

If you are just starting learning SQL, I would strongly suggest to NOT start with some nasty gimmicks like pivoting. ;-) However, it CAN be done, but it's not that easy if you never worked with SQL before. — Tyron78
– Tyron78, Commented Jul 13, 2016 at 13:14
Concerning pivoting, this might help: oracle.com/technetwork/articles/sql/11g-pivot-097235.html — Tyron78
– Tyron78, Commented Jul 13, 2016 at 13:16

Aleksej · Accepted Answer · 2016-07-13 13:21:36Z

1

One way could be the following, computing the concat of phone numbers partitioning by ID:

with Table1(PERSON_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME) as
(
  select 1, 'John', 'Carter', 'Jones' from dual union all
  select 2, 'James', '', 'Smith' from dual 
),
Table2(PERSON_ID, PHONE_NUMBER, PHONE_TYPE) as
(
  select 1,'111-111-1111',   'HOME' from dual union all
  select 1,'111-111-1112',   'PERSONAL' from dual union all
  select 1,'111-111-1113',   'BUSINESS' from dual union all
  select 2,'222-222-2221',   'BUSINESS' from dual union all
  select 2,'222-222-2223',   'BUSINESS' from dual 
)
select distinct listagg(PHONE_NUMBER, ', ') within group (order by phone_number) over (partition by person_id), PERSON_ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME
from table1 
  inner join table2
   using(person_id)

answered Jul 13, 2016 at 13:21

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL query select one to many, mult row into one row result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related