normalize multi-value data field into new table 1

[landolakes]

I'm stuck trying to figure out how to easily take a specific delimited field of values, and populate another similar table that has the values broken out (see below). This forum was very helpful when I had to process data in the other direction, but I have not been able to figure out the reverse of that.

Almost thought I had it looking at thread186-1291312, but not quite. I do have access to create procedures which is how I figured this may be accomplished.



Here is the TMP_DENORM (source) table:
NBR NAME CERTS
1001 joe TI BA OC
1002 bob TI BK
1003 jim TI LV



Here is what the TMP_NORM (destination) table needs to look like:
NBR NAME CERTS
1001 joe TI
1001 joe BA
1001 joe OC
1002 bob TI
1002 bob BK
1003 jim TI
1003 jim LV

CREATE TABLE TMP_NORM
( NBR    INTEGER,
  NAME   VARCHAR2(20 BYTE),
  CERTS  VARCHAR2(100 BYTE) );


CREATE TABLE TMP_DENORM
( NBR    INTEGER,
  NAME   VARCHAR2(20 BYTE),
  CERTS  VARCHAR2(100 BYTE),
  PRIMARY KEY
 (NBR) );


INSERT INTO TMP_DENORM ( NBR, NAME, CERTS ) VALUES ( 
1001, 'joe', 'TI BA OC'); 
INSERT INTO TMP_DENORM ( NBR, NAME, CERTS ) VALUES ( 
1002, 'bob', 'TI BK'); 
INSERT INTO TMP_DENORM ( NBR, NAME, CERTS ) VALUES ( 
1003, 'jim', 'TI LV'); 
commit;

 

[BJCooperIT]

Are multiple CERTS always 2 characters separated by a space? If so, you can force it in:

insert into TMP_NORM
values (select NBR, NAME, substr(CERTS,1,2)
        from   TMP_DENORM
        where  length(certs) = 2);

insert into TMP_NORM
values (select NBR, NAME, substr(CERTS,4,2)
        from   TMP_DENORM
        where  length(certs) = 5);

insert into TMP_NORM
values (select NBR, NAME, substr(CERTS,7,2)
        from   TMP_DENORM
        where  length(certs) = 8);

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

LandO,

Thank you, Thank you, Thank you for including the code to re-create your environment...That saves so much time for those who wish to help you.

Actually, the code to which you referred (in thread186-1291312) works fine. I've plagiarised that code, below, and tailored it to your tables:

set feedback off
set echo off
var x char(55)
col most new_value most noprint
col x new_value x noprint
set termout off
select '@_,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' x from dual;
select max(nvl(length(translate(certs,'&x','@')),0)+1) most from TMP_DENORM;
set termout on
col certs format a5
col name format a4
drop table tmp_norm;

Table dropped.

create table tmp_norm as
select nbr
      ,name -- ,rn,currmax
      ,substr(certs
                   ,decode(rn,1,1
                               ,instr(certs,' ',1,rn-1)+1
                          )
                   ,decode(rn,currmax,length(certs)
                                     ,decode(rn,1,instr(certs,' ')-1
                                                 ,instr(certs,' ',1,rn)-
                                                  instr(certs,' ',1,rn-1)-1
                                            )
                          )
                   ) certs
from
(select p1.*, rn, nvl(length(translate(certs,'&x','@')),0)+1 currmax
  from TMP_DENORM p1
      ,(select rownum rn from all_tab_columns n
         where rownum <= &most)
 where rn <= nvl(length(translate(certs,'&x','@')),0)+1
)
/

Table created.

select * from tmp_norm
 order by nbr, name, certs
;

  NBR NAME CERTS
----- ---- -----
 1001 joe  BA
 1001 joe  OC
 1001 joe  TI
 1002 bob  BK
 1002 bob  TI
 1003 jim  LV
 1003 jim  TI

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[landolakes]

Sorry - got called out of town unexpectedly last week. Thanks both for replying, I'll post an update when I try the code.

 

[landolakes]

Very nice. Translation from my "cert" example to my real data works AOK. Thanks again!