Break one column to multiple columns

[sree_sri]

Hi Stecal,

Thanks for sucking idea, Surely Jmodic gave me very good
start. I think i can solve this problem with out sucking.

Anyway once again thanks for valuable time and valuable
suggestion.

Sree.

[sree_sri]

Hi Jmodic&Stecal,

I modified that code like this and it's working for 3 columns.

SELECT
substr(dname,1,INSTR(dname, ' ', 1, 1))
AS column_1,
substr(dname,INSTR(dname, ' ', 1, 1)+1,decode(instr(dname,' ',instr(dname, ' ',1,1)+1,1),0,50,instr(dname,' ',instr(dname, ' ',1,1)+1,1))-
INSTR(dname, ' ', 1, 1))
AS column_2,

SUBSTR(dname, DECODE(INSTR(dname, ' ', 1, 2),
0, LENGTH(dname)+1,
INSTR(dname, ' ', 1, 2)+1)) AS column_3
from dept
;

Now result looking like this.

< ------------------- ----------------- -----------
harry peter
mag b boston
Keith warn

Thanks Jmodic, Now i am working for 4 columns.

Sree.

[sree_sri]

Hi Jmodic,

I successfully divided one column into 4 columns. This is the code.

SELECT

SUBSTR(DNAME,1,INSTR(dname, ' ', 1, 1)) AS COLUMN_1,


SUBSTR(DNAME,INSTR(dname, ' ', 1, 1),
DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1),0,70,
INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1))-INSTR(dname, ' ', 1, 1)) AS COLUMN_2,



SUBSTR(DNAME,DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1),0,70,
INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1)),
DECODE(INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1),0,70,INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1)
)-INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1), 1)
) AS COLUMN_3,


SUBSTR(DNAME,
DECODE(INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1),0,70,INSTR(dname, ' ',DECODE(INSTR(dname, ' ', instr(dname,' ',instr(dname, '
',1,1)+1,1)),0,0,INSTR(dname, ' ', instr(dname,' ',instr(dname, ' ',1,1)+1,1))+1),1))) AS COLUMN_4

from dept;

My data in table is

Full Name
------------
Equips0 1
CHAS AGOT
JUAN ALBOR
MARIANN ALEMAN
WING KEEN ALVIN SEE
PAUL ANDERSON
SCOTT L ANDERTON
BOUNPONE APHAY
DENNIS ATTAR

When i ran that query data is displaying like this.
COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
------------- -------- ---------- --------------
Equips0 1
CHAS AGOT
JUAN ALBOR
MARIANN ALEMAN
WING KEEN ALVIN SEE
PAUL ANDERSON
SCOTT L ANDERTON
BOUNPONE APHAY
DENNIS ATTAR


I think this script will solve my problem, Thanks for giving
me push on this. Really i appriciate your help. Bye..

Sree.