Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2010
    Posts
    40

    Unanswered: Splitting a row in Mulitple columns

    Hi All,

    I am new to db2 and I know I have done this in mysql but not sure if it can be done in db2 too with a sql query. I need to put the data of one column ( a row ) into multiple columns. How can I do that using a sql query . Please let me know

    Regards

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I thought that there were already some threads in this forum discussing to decompose a string into a column of multiple rows,
    I couldn't find that.

    If you could that, it is easy to transform rows into columns.
    See "PIVOT" in "SQL on Fire! Part 1"
    http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

    Anyway, if you published your sql code in mysql and DB2 version/release and platform OS on which you were working,
    it might be easy to migrate the code to DB2.
    Last edited by tonkuma; 12-20-11 at 00:13. Reason: Correct some English.

  3. #3
    Join Date
    Nov 2010
    Posts
    40

    Red face

    Hi Tonkuma,

    Basically what I am trying to do is within a row trying to extract data between two funny characters and put it in a separate column using a sql. The funny characters are '@' and '/' . I am trying to search the web but didnot find an example for this. Could you please help,

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't see anything particularly funny about the at symbol and the forward slash, but you can use either LOCATE() or INSTR() function to determine their positions, then SUBSTR() to extract the substring you need.

  5. #5
    Join Date
    Nov 2010
    Posts
    40
    ok I got it working until where it can find every occurrence of the first character and put it in a separate column but now I am stuck where I need to find the occurrence of the second character and get rid of the rest of the data since the rest of the data goes into its own column, using the locate and substr

    thanks

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please supply sample data and expected results.
    The sample data should have enough rows to include various exceptional conditions.

  7. #7
    Join Date
    Nov 2010
    Posts
    40
    ok now there is a different issue with this, so I had to change the
    query, now I am trying to extract data between the '\n' and a ':'

    so here is my query for that

    select id, text
    , 1 rk
    , substr( text,locate_in_string(text,':',1,1)+1
    ,locate_in_string(text,chr(10),1,1)-(locate_in_string(text,':',1,1)+1)) first

    --, substr(text,locate_in_string(text,chr(10),1,1) + 1,
    --length(text) - locate_in_string(text,chr(10),1,1) + 1 ) rem
    from db2inst1.cri_customer
    --where id in ( 44,44,46);


    ERROR : A numeric argument of a built-in string function is out of range..
    SQLCODE=-138, SQLSTATE=22011, DRIVER=3.58.81



    when I run this for above ids it runs fine but when I run for all the ids
    it runs out of range, my placement for chr(10) is not quite right. Could you help me

    thanks

  8. #8
    Join Date
    Nov 2010
    Posts
    40
    Here is some sample data

    Approval list: Members
    Tenant type: Tenant
    Reason Code: Not accepted due variety of Reasons
    Refer: Document No1

    I am extracting data between : and \n now

    thanks

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The following example assumed that characters ':' and new line(CHR(10) or x'0A') were paired with same order.

    Example 1: added some test data
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH cri_customer(id , text) AS (
    VALUES ( 1 ,
    'Approval list: Members' || x'0A' ||
    'Tenant type: Tenant' || x'0A' ||
    'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
    'Refer: Document No1' )
    , ( 101 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 1' || x'0A' ||
    'Title: A Kiss for Little Bear' || x'0A' ||
    'Author: Else Homelund Minarik' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 102 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 2' || x'0A' ||
    'Title: The Moon Jumpers' || x'0A' ||
    'Author: Janice May Udry' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 103 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 3' || x'0A' ||
    'Title: The Goodnight Gecko' || x'0A' ||
    'Author: Gill McBarnet' || x'0A' ||
    'Pictures: Gill McBarnet' || x'0A' ||
    'Note: Published by Hawaiian publisher' )
    , ( 999 , 'garbage?' || x'0A' || 'xxxx' )
    )
    /*
    SELECT * FROM cri_customer
    */
    SELECT id
         , SMALLINT( (rnum + 1) / 2 ) AS line
         , SUBSTR( MAX(text)
                 , MIN(k) + 1
                 , NULLIF( MAX(k) , MIN(k) ) - MIN(k) - 1
                 ) AS extracted
     FROM  cri_customer
     LEFT  OUTER JOIN
           LATERAL
           (SELECT k
                 , ROW_NUMBER() OVER(ORDER BY k) AS rnum
             FROM  LATERAL
                   (SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
                     FROM  (VALUES  1, 2, 3, 4, 5, 6, 7, 8
                                  , 9,10,11,12,13,14,15,16 ) k(k1)
                     INNER JOIN
                           (VALUES   0, 16, 32, 48, 64, 80, 96,112
                                  ,128,144,160,176,192,208,224,240 ) k(k2)
                       ON  k1 + k2 <= LENGTH(text) + 1
                     INNER JOIN
                           (VALUES    0, 256, 512, 768,1024,1280,1536,1792
                                  ,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
                       ON  k1 + k2 + k3 <= LENGTH(text) + 1
                   ) k(k)
             WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
           )
       ON  MOD(rnum , 2)        = 0
       OR  SUBSTR(text , k , 1) = ':'
     GROUP BY
           id
         , (rnum + 1) / 2
     ORDER BY
           id
         , line
    ;
    ------------------------------------------------------------------------------
    
    ID          LINE   EXTRACTED                                                                                                                                                                             
    ----------- ------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1      1  Members                                                                                                                                                                              
              1      2  Tenant                                                                                                                                                                               
              1      3  Not accepted due variety of Reasons                                                                                                                                                  
              1      4  Document No1                                                                                                                                                                         
            101      1  Personal library                                                                                                                                                                     
            101      2  Picture Books                                                                                                                                                                        
            101      3  1                                                                                                                                                                                    
            101      4  A Kiss for Little Bear                                                                                                                                                               
            101      5  Else Homelund Minarik                                                                                                                                                                
            101      6  Maurice Sendak                                                                                                                                                                       
            102      1  Personal library                                                                                                                                                                     
            102      2  Picture Books                                                                                                                                                                        
            102      3  2                                                                                                                                                                                    
            102      4  The Moon Jumpers                                                                                                                                                                     
            102      5  Janice May Udry                                                                                                                                                                      
            102      6  Maurice Sendak                                                                                                                                                                       
            103      1  Personal library                                                                                                                                                                     
            103      2  Picture Books                                                                                                                                                                        
            103      3  3                                                                                                                                                                                    
            103      4  The Goodnight Gecko                                                                                                                                                                  
            103      5  Gill McBarnet                                                                                                                                                                        
            103      6  Gill McBarnet                                                                                                                                                                        
            103      7  Published by Hawaiian publisher                                                                                                                                                      
            999      1 -                                                                                                                                                                                     
    
      24 record(s) selected.
    Last edited by tonkuma; 12-23-11 at 10:53. Reason: Replace last test data("999,garbage?") and ON condition.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Calculate once the expression "(rnum + 1) / 2" in select-list and group by clause.

    Example 1a:
    Code:
    SELECT id
         , line
         , SUBSTR( MAX(text)
                 , MIN(k) + 1
                 , NULLIF( MAX(k) , MIN(k) ) - MIN(k) - 1
                 ) AS extracted
     FROM  cri_customer
     LEFT  OUTER JOIN
           LATERAL
           (SELECT k
                 , ROW_NUMBER() OVER(ORDER BY k) AS rnum
             FROM  LATERAL
                   (SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
                     FROM  (VALUES  1, 2, 3, 4, 5, 6, 7, 8
                                  , 9,10,11,12,13,14,15,16 ) k(k1)
                     INNER JOIN
                           (VALUES   0, 16, 32, 48, 64, 80, 96,112
                                  ,128,144,160,176,192,208,224,240 ) k(k2)
                       ON  k1 + k2 <= LENGTH(text) + 1
                     INNER JOIN
                           (VALUES    0, 256, 512, 768,1024,1280,1536,1792
                                  ,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
                       ON  k1 + k2 + k3 <= LENGTH(text) + 1
                   ) k(k)
             WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
           )
       ON  MOD(rnum , 2) = 0
       OR  SUBSTR(text , k , 1) = ':'
     CROSS JOIN
           LATERAL
           (VALUES SMALLINT( (rnum + 1) / 2 ) ) f(line)
     GROUP BY
           id
         , line
     ORDER BY
           id
         , line
    ;

  11. #11
    Join Date
    Nov 2010
    Posts
    40
    Hi Tonkuma,

    This method works like a charm, but

    1. The occurence of \n is not fixed length, it varies for the rows

    3. there are rows in the column for some ids that are in
    one straight line and it doesnot have occurence of any of the above
    characters in it. I am getting Null value for those rows.


    ID LINE EXTRACTED
    15 NULL NULL
    47 NULL NULL



    2. the query extract the data in seperate rows I need to put them in separate columns

    thanks

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1. The occurence of \n is not fixed length, it varies for the rows
    No problem.
    I showed some data with different number of lines and different length of each lines in test data of Example 1.

    2. the query extract the data in seperate rows I need to put them in separate columns
    I already wrote "it is easy to transform rows into columns".
    See ...
    Quote Originally Posted by tonkuma View Post
    ...
    ... to decompose a string into a column of multiple rows, ...

    If you could that, it is easy to transform rows into columns.
    See "PIVOT" in "SQL on Fire! Part 1"
    http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

    ...

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    3. there are rows in the column for some ids that are in
    one straight line and it doesnot have occurence of any of the above
    characters in it. I am getting Null value for those rows.


    ID LINE EXTRACTED
    15 NULL NULL
    47 NULL NULL
    I made consciously the query that way.
    See id = 999 in Example 1.

    Now, I understand that I have misunderstood your requirements.
    It would be easy to modify the query.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please look and check the test data and the results of id = (905, 907, 996, 997).

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH cri_customer(id , text) AS (
    VALUES ( 1 ,
    'Approval list: Members' || x'0A' ||
    'Tenant type: Tenant' || x'0A' ||
    'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
    'Refer: Document No1' )
    , ( 101 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 1' || x'0A' ||
    'Title: A Kiss for Little Bear' || x'0A' ||
    'Author: Else Homelund Minarik' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 102 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 2' || x'0A' ||
    'Title: The Moon Jumpers' || x'0A' ||
    'Author: Janice May Udry' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 103 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 3' || x'0A' ||
    'Title: The Goodnight Gecko' || x'0A' ||
    'Author: Gill McBarnet' || x'0A' ||
    'Pictures: Gill McBarnet' || x'0A' ||
    'Note: Published by Hawaiian publisher' )
    , ( 905 ,
    'one straight line shoud display as it is.' )
    , ( 907 ,
    ' 1: Frst line data' || x'0A' ||
    ' 2: Second line data' || x'0A' ||
    ' ... rest of line without colon' )
    , ( 996 ,
    'first line without colon, followed by some lines without colon ... garbage?' || x'0A' ||
    'second line without colon' || x'0A' ||
    'third line without colon' ) /* multiple lines without colon */
    , ( 997 ,
    'first line without colon, followed by some lines(some of them having colon) ... garbage?' || x'0A' ||
    'second line: having colon' ) /* multiple lines with some front lines having no colon */
    )
    /*
    SELECT * FROM cri_customer
    */
    SELECT id
         , COALESCE(line , 1) AS line
         , SUBSTR( text
                 , COALESCE( MIN(k) , 0 ) + 1
                 , NULLIF( COALESCE( MAX(k) , LENGTH(text) + 1 ) , MIN(k) ) - COALESCE( MIN(k) , 0 ) - 1
                 ) AS extracted
     FROM  cri_customer
     LEFT  OUTER JOIN
           LATERAL
           (SELECT k
                 , ROW_NUMBER() OVER(ORDER BY k) AS rnum
             FROM  LATERAL
                   (SELECT k1 + k2 + k3 /* maximun length of text = 4096 */
                     FROM  (VALUES  1, 2, 3, 4, 5, 6, 7, 8
                                  , 9,10,11,12,13,14,15,16 ) k(k1)
                     INNER JOIN
                           (VALUES   0, 16, 32, 48, 64, 80, 96,112
                                  ,128,144,160,176,192,208,224,240 ) k(k2)
                       ON  k1 + k2 <= LENGTH(text) + 1
                     INNER JOIN
                           (VALUES    0, 256, 512, 768,1024,1280,1536,1792
                                  ,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
                       ON  k1 + k2 + k3 <= LENGTH(text) + 1
                   ) k(k)
             WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
           )
       ON  MOD(rnum , 2) = 0
       OR  SUBSTR(text , k , 1) = ':'
     CROSS JOIN
           LATERAL
           (VALUES SMALLINT( (rnum + 1) / 2 ) ) f(line)
     GROUP BY
           id
         , line
         , text
     ORDER BY
           id
         , line
    ;
    ------------------------------------------------------------------------------
    
    ID          LINE        EXTRACTED                                                                                                                                                                             
    ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1           1  Members                                                                                                                                                                              
              1           2  Tenant                                                                                                                                                                               
              1           3  Not accepted due variety of Reasons                                                                                                                                                  
              1           4  Document No1                                                                                                                                                                         
            101           1  Personal library                                                                                                                                                                     
            101           2  Picture Books                                                                                                                                                                        
            101           3  1                                                                                                                                                                                    
            101           4  A Kiss for Little Bear                                                                                                                                                               
            101           5  Else Homelund Minarik                                                                                                                                                                
            101           6  Maurice Sendak                                                                                                                                                                       
            102           1  Personal library                                                                                                                                                                     
            102           2  Picture Books                                                                                                                                                                        
            102           3  2                                                                                                                                                                                    
            102           4  The Moon Jumpers                                                                                                                                                                     
            102           5  Janice May Udry                                                                                                                                                                      
            102           6  Maurice Sendak                                                                                                                                                                       
            103           1  Personal library                                                                                                                                                                     
            103           2  Picture Books                                                                                                                                                                        
            103           3  3                                                                                                                                                                                    
            103           4  The Goodnight Gecko                                                                                                                                                                  
            103           5  Gill McBarnet                                                                                                                                                                        
            103           6  Gill McBarnet                                                                                                                                                                        
            103           7  Published by Hawaiian publisher                                                                                                                                                      
            905           1 one straight line shoud display as it is.                                                                                                                                             
            907           1  Frst line data                                                                                                                                                                       
            907           2  Second line data                                                                                                                                                                     
            996           1 -                                                                                                                                                                                     
            997           1 -                                                                                                                                                                                     
    
      28 record(s) selected.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3: Extract data into rows by using Example 1 or 2, then convert rows to columns.
    The code from previous examples was modified a little to meet the requirements and to eliminate repeated calculation of same expression.

    Note: CAST(extracted AS VARCHAR(41) ) was used for ease of see of the result.
    41 is the maximum length of extracted string in test data.
    You may want to make it larger or want to eliminate casting itself.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH cri_customer(id , text) AS (
    VALUES ( 1 ,
    'Approval list: Members' || x'0A' ||
    'Tenant type: Tenant' || x'0A' ||
    'Reason Code: Not accepted due variety of Reasons' || x'0A' ||
    'Refer: Document No1' )
    , ( 101 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 1' || x'0A' ||
    'Title: A Kiss for Little Bear' || x'0A' ||
    'Author: Else Homelund Minarik' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 102 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 2' || x'0A' ||
    'Title: The Moon Jumpers' || x'0A' ||
    'Author: Janice May Udry' || x'0A' ||
    'Pictures: Maurice Sendak' )
    , ( 103 ,
    'Book list: Personal library' || x'0A' ||
    'Book type: Picture Books' || x'0A' ||
    'Sequence number: 3' || x'0A' ||
    'Title: The Goodnight Gecko' || x'0A' ||
    'Author: Gill McBarnet' || x'0A' ||
    'Pictures: Gill McBarnet' || x'0A' ||
    'Note: Published by Hawaiian publisher' )
    , ( 905 ,
    'one straight line shoud display as it is.' )
    , ( 907 ,
    ' 1: Frst line data' || x'0A' ||
    ' 2: Second line data' || x'0A' ||
    ' ... rest of line without colon' )
    , ( 996 ,
    'first line without colon, followed by some lines without colon ... garbage?' || x'0A' ||
    'second line without colon' || x'0A' ||
    'third line without colon' ) /* multiple lines without colon */
    , ( 997 ,
    'first line without colon, followed by some lines(some of them having colon) ... garbage?' || x'0A' ||
    'second line: having colon' ) /* multiple lines with some front lines having no colon */
    )
    /*
    SELECT * FROM cri_customer
    */
    , elements_in_rows AS (
    SELECT id
         , line
         , CASE WHEN MAX(k) > MIN(k) THEN SUBSTR(text , MIN(k) + 1 , MAX(k) - MIN(k) - 1)
                WHEN MAX(k) IS NULL  THEN text
           END  AS extracted
     FROM  cri_customer
     CROSS JOIN LATERAL
           (VALUES LENGTH(text) + 1 ) f(text_len_plus)
     LEFT  OUTER JOIN LATERAL
           (SELECT k
                 , ROW_NUMBER() OVER(ORDER BY k) AS rnum
             FROM  LATERAL
                   (SELECT k1 + k2 + k3 AS k /* maximun length of text = 4095 */
                     FROM  (VALUES  1, 2, 3, 4, 5, 6, 7, 8
                                  , 9,10,11,12,13,14,15,16 ) k(k1)
                     INNER JOIN
                           (VALUES   0, 16, 32, 48, 64, 80, 96,112
                                  ,128,144,160,176,192,208,224,240 ) k(k2)
                       ON  k1 + k2 <= text_len_plus
                     INNER JOIN
                           (VALUES    0, 256, 512, 768,1024,1280,1536,1792
                                  ,2048,2304,2560,2816,3072,3328,3584,3840 ) k(k3)
                       ON  k1 + k2 + k3 <= text_len_plus
                   ) k
             WHERE SUBSTR(text || x'0A' , k , 1) IN(':' , x'0A')
           )
       ON  MOD(rnum , 2)        = 0
       OR  SUBSTR(text , k , 1) = ':'
     CROSS JOIN LATERAL
           (VALUES COALESCE( SMALLINT( (rnum + 1) / 2 ) , 1 ) ) f(line)
     GROUP BY
           id
         , line
         , text
    )
    SELECT id
         , MAX( CASE line WHEN  1 THEN extracted END ) AS first_line
         , MAX( CASE line WHEN  2 THEN extracted END ) AS second_line
         , MAX( CASE line WHEN  3 THEN extracted END ) AS third_line
         , MAX( CASE line WHEN  4 THEN extracted END ) AS fourth_line
         , MAX( CASE line WHEN  5 THEN extracted END ) AS fifth_line
         , MAX( CASE line WHEN  6 THEN extracted END ) AS sixth_line
         , MAX( CASE line WHEN  7 THEN extracted END ) AS seventh_line
         , MAX( CASE line WHEN  8 THEN extracted END ) AS eighth_line
         , MAX( CASE line WHEN  9 THEN extracted END ) AS nineth_line
         , MAX( CASE line WHEN 10 THEN extracted END ) AS tenth_line
     FROM  (SELECT id , line
                 , CAST(extracted AS VARCHAR(41) ) AS extracted
             FROM  elements_in_rows
           )
     GROUP BY
           id
     ORDER BY
           id
    ;
    ------------------------------------------------------------------------------
    
    ID          FIRST_LINE                                SECOND_LINE                               THIRD_LINE                                FOURTH_LINE                               FIFTH_LINE                                SIXTH_LINE                                SEVENTH_LINE                              EIGHTH_LINE                               NINETH_LINE                               TENTH_LINE                               
    ----------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- ----------------------------------------- -----------------------------------------
              1  Members                                   Tenant                                    Not accepted due variety of Reasons       Document No1                             -                                         -                                         -                                         -                                         -                                         -                                        
            101  Personal library                          Picture Books                             1                                         A Kiss for Little Bear                    Else Homelund Minarik                     Maurice Sendak                           -                                         -                                         -                                         -                                        
            102  Personal library                          Picture Books                             2                                         The Moon Jumpers                          Janice May Udry                           Maurice Sendak                           -                                         -                                         -                                         -                                        
            103  Personal library                          Picture Books                             3                                         The Goodnight Gecko                       Gill McBarnet                             Gill McBarnet                             Published by Hawaiian publisher          -                                         -                                         -                                        
            905 one straight line shoud display as it is. -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                        
            907  Frst line data                            Second line data                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                        
            996 -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                        
            997 -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                         -                                        
    
      8 record(s) selected.
    Last edited by tonkuma; 12-24-11 at 05:45.

Posting Permissions

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