Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table customers (id, customer_name) as
select 1, 'AAA' from dual
union all
select 2, 'BBB' from dual
2 rows affected

create table contacts (id, customer_id, contact_no) as
select 1, 1, '1001' from dual
union all
select 2, 1, '1002' from dual
union all
select 3, 1, '1003' from dual
union all
select 4, 2, '2001' from dual
union all
select 5, 2, '2002' from dual
5 rows affected

select cus.id as customer_id,
  cus.customer_name,
  con.contact_no,
  row_number() over (partition by cus.id order by con.id) as rn
from customers cus
left join contacts con on con.customer_id = cus.id


CUSTOMER_ID
CUSTOMER_NAME
CONTACT_NO
RN


1
AAA
1001
1

1
AAA
1002
2

1
AAA
1003
3

2
BBB
2001
1

2
BBB
2002
2


select *
from (
  select cus.id as customer_id,
    cus.customer_name,
    con.contact_no,
    row_number() over (partition by cus.id order by con.id) as rn
  from customers cus
  left join contacts con on con.customer_id = cus.id
)
pivot (max(contact_no) as contact_no for (rn) in (1, 2, 3, 4, 5))


CUSTOMER_ID
CUSTOMER_NAME
1_CONTACT_NO
2_CONTACT_NO
3_CONTACT_NO
4_CONTACT_NO
5_CONTACT_NO


2
BBB
2001
2002
null
null
null

1
AAA
1001
1002
1003
null
null


select *
from (
  select cus.id as customer_id,
    cus.customer_name,
    con.contact_no,
    row_number() over (partition by cus.id order by con.id) as rn
  from customers cus
  left join contacts con on con.customer_id = cus.id
)
pivot (max(contact_no) for (rn) in (1 as contact_no_1, 2 as contact_no_2,
  3 as contact_no_3,  4 as contact_no_4, 5 as contact_no_5))


CUSTOMER_ID
CUSTOMER_NAME
CONTACT_NO_1
CONTACT_NO_2
CONTACT_NO_3
CONTACT_NO_4
CONTACT_NO_5


2
BBB
2001
2002
null
null
null

1
AAA
1001
1002
1003
null
null


select customer_id,
  customer_name,
  max(case when rn = 1 then contact_no end) as contact_no_1,
  max(case when rn = 2 then contact_no end) as contact_no_2,
  max(case when rn = 3 then contact_no end) as contact_no_3,
  max(case when rn = 4 then contact_no end) as contact_no_4,
  max(case when rn = 5 then contact_no end) as contact_no_5
from (
  select cus.id as customer_id,
    cus.customer_name,
    con.contact_no,
    row_number() over (partition by cus.id order by con.id) as rn
  from customers cus
  left join contacts con on con.customer_id = cus.id
)
group by customer_id, customer_name


CUSTOMER_ID
CUSTOMER_NAME
CONTACT_NO_1
CONTACT_NO_2
CONTACT_NO_3
CONTACT_NO_4
CONTACT_NO_5


2
BBB
2001
2002
null
null
null

1
AAA
1001
1002
1003
null
null