Re: help with sql

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 30 Jul 2006 08:40:51 +0200
Message-ID: <44cc5474$0$29875$626a54ce@news.free.fr>

"Brian (withholding surname)" <brianb000001_at_hotmail.com> a écrit dans le message de news: 1152184122.310119.86150_at_p79g2000cwp.googlegroups.com...
| Hi, I am using Java to query 10g database. I have table containing this
| information
|
| ID EMAILTO
| ---- ---------------------------------
| 1 Sangster, Derek; Williams, Mary G; Phillpot, Mark
| 17 Williams, Mary G
| 29 Cunningham, Peter; Richards; Peter
|
| I want to turn this into columns LASTNAME and FIRSTNAME
| 1 Sangster Derek
| 1 Williams Mary G
| 1 Phillpot Mark
| 17 Williams Mary G
| 29 Cunningham Peter
| 29 Richards Peter
|
| I am query database so can only use SQL and not procedures PLSQL. How
| can I do this? URGENT.
|
| Brian
|

SQL> select * from t order by id;

        ID EMAILTO

---------- ------------------------------------------------------------
         1 Sangster, Derek;  Williams, Mary G; Phillpot, Mark
        17 Williams, Mary G
        29 Cunningham, Peter; Richards, Peter

3 rows selected.

SQL> col firstname format a12
SQL> col lastname format a12
SQL> with

  2 data as ( select id, ';'||emailto||';' emailto from t ),   3 maxval as (

  4      select max(length(emailto)-length(replace(emailto,';','')))-1 maxval
  5      from data

  6 ),
  7 lines as ( select rownum rn from dual, maxval connect by level <= maxval ),   8 wholenames as (

  9      select id, rn,
 10             trim(substr(emailto,
 11                         instr(emailto,';',1,rn)+1,
 12                         instr(emailto,';',1,rn+1)-instr(emailto,';',1,rn)-1))
 13               name
 14      from data, lines
 15      where rn < length(emailto)-length(replace(emailto,';',''))

 16 )
 17 select id,

 18         trim(substr(name,1,instr(name,',')-1)) lastname,
 19         trim(substr(name,instr(name,',')+1)) firstname

 20 from wholenames
 21 order by id, rn
 22 /

        ID LASTNAME FIRSTNAME
---------- ------------ ------------

         1 Sangster     Derek
         1 Williams     Mary G
         1 Phillpot     Mark
        17 Williams     Mary G
        29 Cunningham   Peter
        29 Richards     Peter

6 rows selected.

Regards
Michel Cadot Received on Sun Jul 30 2006 - 01:40:51 CDT