Break one column to multiple columns - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Break one column to multiple columns

  1. #11
    Join Date
    Feb 2001
    Posts
    203
    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

  2. #12
    Join Date
    Feb 2001
    Posts
    203
    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

  3. #13
    Join Date
    Feb 2001
    Posts
    203
    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.

    sree

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width