CODEWITHSUNDEEP
sqloracle10g
user2249376
user2249376

Reputation: 5

How do I return multiple column values as new rows in Oracle 10g?

I have a table where multiple account numbers are associated with different IDs(DR_NAME). Each account could have as few as 0 accounts, and as many as 16. I believe UNPIVOT would work, but I'm on Oracle 10g, which does not support this. 

DR_NAME   ACCT1   ACCT2   ACCT3   ACC4  
======================================
SMITH     1234
JONES     5678    2541    2547
MARK      NULL    
WARD      8754    6547

I want to display a new line for each name with only 1 account number per line

DR_NAME   ACCT
==============
SMITH     1234
JONES     5678
JONES     2541
JONES     2547
MARK      NULL
WARD      8754
WARD      6547

Upvotes: 0

Views: 5098

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

If you're only interested in inserting these records then take a look at multitable insert -- a single scan of the data and multiple rows generated, so it's very efficient.

Code examples here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF01604

Note that you can reference the same table multiple times, using syntax along the lines of ...

insert all
  when acct1 is not null then into target_table (..) values (dr_name,acct1)
  when acct2 is not null then into target_table (..) values (dr_name,acct2)
  when acct3 is not null then into target_table (..) values (dr_name,acct3)
  when acct4 is not null then into target_table (..) values (dr_name,acct4)
select
  dr_name,
  acct1,
  acct2,
  acct3,
  acct4
from my_table.

Upvotes: 1

Taryn
Taryn

Reputation: 247840

Oracle 10g does not have an UNPIVOT function but you can use a UNION ALL query to unpivot the columns into rows:

select t1.DR_NAME, d.Acct
from yourtable t1
left join
(
  select DR_NAME, ACCT1 as Acct
  from yourtable
  where acct1 is not null
  union all
  select DR_NAME, ACCT2 as Acct
  from yourtable
  where acct2 is not null
  union all
  select DR_NAME, ACCT3 as Acct
  from yourtable
  where acct3 is not null
  union all
  select DR_NAME, ACCT4 as Acct
  from yourtable
  where acct4 is not null
) d
  on t1.DR_NAME = d.DR_NAME;

See SQL Fiddle with Demo.

This query uses a UNION ALL to convert the columns into rows. I included a where clause to remove any null values, otherwise you will get multiple rows for each account where the acct value is null. Excluding the null values will drop the dr_name = Mark which you showed that you want in the final result. To include the rows that only have null values, I added the join to the table again.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

The most efficient way I know is to do a cross join with some logic:

select *
from (select t.dr_name,
             (case when n.n = 1 then acct1
                   when n.n = 2 then acct2
                   when n.n = 3 then acct3
                   when n.n = 4 then acct4
              end) as acct
      from t cross join
           (select 1 as n from dual union all
            select 2 from dual union all
            select 3 from dual union all
            select 4 from dual
           ) n
     ) s
where acct is not null

The union all approach typical results in scanning the table once for each subquery. This approach will typically scan the table once.

Upvotes: 1

Related Questions