Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Transpose column into columns

    create table #temp
    (name varchar(50)
    )


    insert into #temp values ('karthik~gokul~siva~magesh~')
    insert into #temp values ('kar~go~sa~mag~')
    insert into #temp values ('k~g~s~m~')

    Expected output:

    c1 c2 c3 c4
    karthik gokul siva magesh
    kar go sa mag
    k g s m

    Inputs are welcome!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Assuming DB2 for LUW 9.7.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT name
         , SUBSTR(name ,      1 , p1      - 1) AS c1
         , SUBSTR(name , p1 + 1 , p2 - p1 - 1) AS c2
         , SUBSTR(name , p2 + 1 , p3 - p2 - 1) AS c3
         , SUBSTR(name , p3 + 1 , p4 - p3 - 1) AS c4
      FROM (SELECT name
                 , INSTR(name , '~' , 1 , 1) AS p1
                 , INSTR(name , '~' , 1 , 2) AS p2
                 , INSTR(name , '~' , 1 , 3) AS p3
                 , INSTR(name , '~' , 1 , 4) AS p4
              FROM #temp
            );
    ------------------------------------------------------------------------------
    
    NAME                                               C1                                                 C2                                                 C3                                                 C4                                                
    -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
    karthik~gokul~siva~magesh~                         karthik                                            gokul                                              siva                                               magesh                                            
    kar~go~sa~mag~                                     kar                                                go                                                 sa                                                 mag                                               
    k~g~s~m~                                           k                                                  g                                                  s                                                  m                                                 
    
      3 record(s) selected.

Posting Permissions

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