Oracle Row Into Columns

Question

I am having 2 tables, Customers and Customer Contacts table.

Ex: Customers

Column:

Id
Customer Name

Contacts Table

Column

id
customer_id
contact_no

I need to fetch a record by below format.'

Customer Name, contact_no_1, contact_no_2 .... etc.

I'm using oracle 11g.

How many rows for 1 customer can exist in the Contacts table? — Ankit Bajpai, Jun 24 '21 at 14:21
But is there a maximum value for N; or at least a maximum you can reasonably assume? The number of columns in the result has to be known; if it isn't (because you can't limit N) then this changes from a simple static pivot to a more complicated dynamic pivot. — Alex Poole, Jun 24 '21 at 14:36
Ok, let us assume up to 25 records per customer. Can you help me on this — rajsekar, Jun 24 '21 at 15:31
you can check [this](https://stackoverflow.com/questions/tagged/dynamic-pivot%2boracle?tab=Frequent) out — Barbaros Özhan, Jun 24 '21 at 17:23
Do none [these questions](https://stackoverflow.com/search?q=%5Boracle%5D+%5Bsql%5D+rows+into+columns) help? This question seems to be asked daily. If not, sample data and expected results would help a lot. — William Robertson, Jun 24 '21 at 19:20

score 0 · Answer 1 · answered Jun 24 '21 at 17:17

0

You can make use of either case expression or PIVOT to achieve the same.

Please refer to below links

Link1 Link2 Link3

answered Jun 24 '21 at 17:17

Atif

2,011
9
23

None of the links are tagged Oracle – Barbaros Özhan Jun 24 '21 at 17:18
But still queries are more or less can be used in Oracle. Pivot and case expression in Oracle and SQL server are almost identical. – Atif Jun 24 '21 at 17:21
Static pivot queries(containing case when->"conditional aggregation" or containing `pivot` clause) are very similar, but need different tecniques for dynamic queries. You can check [this](https://stackoverflow.com/questions/tagged/dynamic-pivot%2boracle?tab=Frequent) out – Barbaros Özhan Jun 24 '21 at 17:27
I was only referring to Static based on last comment of user `Ok, let us assume up to 25 records per customer. Can you help me on this` – Atif Jun 24 '21 at 17:41
Each DBMS might have different dialects. Depending on the shared links : Oracle DB has no `STUFF`, `QUOTENAME` functions, no square bracketed formatting for columns, `date` is a reserved keyword if used as a column name, no @ sign prefixed declaration for local variables , no `+` sign concatenation operators, no `AS` keyword for aliasing tables or subqueries ..etc. – Barbaros Özhan Jun 24 '21 at 17:48

Alex Poole · Answer 2 · 2021-06-24T17:26:03.880

You can use a ranking function (rank, dense_rank, row_number) to assign a nominal sequential number to each contact number for each customer:

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

and then either use that in a pivot:

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))

or with a manual pivot using aggregation:

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

db<>fiddle with some made-up data.

I've shown handling up to 5 contact numbers; you need to add as many in() values or case expressions as you need to handle whatever value of N you're comfortable limiting the output to. (If you decide you can't set a limit then you'll have to use a dynamic pivot, which is more complicated.)

I've included customer_id in case you can have two customer that happen to have the same name. You don't have to include that in the final projection; in the pivot version that means you will need to list all the columns you do want to include.

Oracle Row Into Columns

2 Answers2