Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Cool Unanswered: Need SQL statement

    Hi Experts,

    I need a help on DB2 SQL statement, my data is mentioned below with '/' delimiter in a single column and the output I want it to put in different columns as shown below.

    Data
    Col A
    /apple
    /apple/banana
    /orange/apple/banana
    /mango/apple/orange/fig


    Output
    Col A Col B Col C Col D
    apple null null null
    apple banana null null
    orange apple banana null
    mango apple orange fig

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Perhaps, we can argue over table/column design issue.

    Apart from the design issue, my first idea was using recursive query.

    It may be easy, if you are used to recursive query.
    So, I'll give other examples.

    Note: I assumed that '/' in first character was guaranteed.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Data(Col_A) AS (
    VALUES
      '/apple'
    , '/apple/banana'
    , '/orange/apple/banana'
    , '/mango/apple/orange/fig'
    )
    SELECT d.col_a
         , MAX( CASE rn
                WHEN 1 THEN
                     SUBSTR(col_a , 2 , lead_k - 2)
                END
              ) AS new_a
         , MAX( CASE rn
                WHEN 2 THEN
                     SUBSTR(col_a , k + 1 , lead_k - k - 1)
                END
              ) AS new_b
         , MAX( CASE rn
                WHEN 3 THEN
                     SUBSTR(col_a , k + 1 , lead_k - k - 1)
                END
              ) AS new_c
         , MAX( CASE rn
                WHEN 4 THEN
                     SUBSTR(col_a , k + 1 , lead_k - k - 1)
                END
              ) AS new_d
     FROM  (SELECT col_a
                 , k
                 , ROW_NUMBER()
                     OVER(PARTITION BY col_a
                              ORDER BY k    ) rn
                 , LEAD(k)
                     OVER(PARTITION BY col_a
                              ORDER BY k    ) lead_k
             FROM  data
             INNER JOIN
                   (VALUES     1, 2, 3, 4, 5, 6, 7, 8, 9
                          ,10,11,12,13,14,15,16,17,18,19
                          ,20,21,22,23,24,25,26,27,28,29
                          ,30,31,32,33,34,45,46,47,38,39
                          ,40,41,42,43,44,45,46,47,48,49) p(k)
              ON  k = 1
              OR  K BETWEEN 2 AND LENGTH(col_a) + 1
              AND SUBSTR(col_a || '/' , MIN( k , LENGTH(col_a) + 1 ) , 1) = '/'
          ) d
     GROUP BY
           col_a
    ;
    ------------------------------------------------------------------------------
    
    COL_A                   NEW_A                   NEW_B                   NEW_C                   NEW_D                  
    ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
    /apple                  apple                   -                       -                       -                      
    /apple/banana           apple                   banana                  -                       -                      
    /mango/apple/orange/fig mango                   apple                   orange                  fig                    
    /orange/apple/banana    orange                  apple                   banana                  -                      
    
      4 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Data(Col_A) AS (
    VALUES
      '/apple'
    , '/apple/banana'
    , '/orange/apple/banana'
    , '/mango/apple/orange/fig'
    )
    SELECT d.col_a
         , SUBSTR(  col_a
                  , 2
                  , INSTR(col_a || '/' , '/' , 1 , 2) - 2
                 ) AS new_a
         , SUBSTR(  col_a
                  , INSTR(col_a , '/' , 1 , 2) + 1
                  , NULLIF( INSTR(col_a || '/' , '/' , 1 , 3) , 0 )
                    -
                    INSTR(col_a , '/' , 1 , 2) - 1
                 ) AS new_b
         , SUBSTR(  col_a
                  , INSTR(col_a , '/' , 1 , 3) + 1
                  , NULLIF( INSTR(col_a || '/' , '/' , 1 , 4) , 0 )
                    -
                    INSTR(col_a , '/' , 1 , 3) - 1
                 ) AS new_c
         , SUBSTR(  col_a
                  , INSTR(col_a , '/' , 1 , 4) + 1
                  , NULLIF( INSTR(col_a || '/' , '/' , 1 , 5) , 0 )
                    -
                    INSTR(col_a , '/' , 1 , 4) - 1
                 ) AS new_d
     FROM  data d
    ;
    ------------------------------------------------------------------------------
    
    COL_A                   NEW_A                   NEW_B                   NEW_C                   NEW_D                  
    ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
    /apple                  apple                   -                       -                       -                      
    /apple/banana           apple                   banana                  -                       -                      
    /orange/apple/banana    orange                  apple                   banana                  -                      
    /mango/apple/orange/fig mango                   apple                   orange                  fig                    
    
      4 record(s) selected.

  3. #3
    Join Date
    Mar 2004
    Posts
    2

    Thumbs up

    Thanks a lot

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 2-1:

    Make Example 2 shorter by using sub-query.
    Code:
    SELECT col_a
         , SUBSTR(col_a , 2         ,        sep_2      - 2        ) AS new_a
         , SUBSTR(col_a , sep_2 + 1 , NULLIF(sep_3 , 0) - sep_2 - 1) AS new_b
         , SUBSTR(col_a , sep_3 + 1 , NULLIF(sep_4 , 0) - sep_3 - 1) AS new_c
         , SUBSTR(col_a , sep_4 + 1 , NULLIF(sep_5 , 0) - sep_4 - 1) AS new_d
     FROM  (SELECT col_a
                 , INSTR(col_a || '/' , '/' , 1 , 2) AS sep_2
                 , INSTR(col_a || '/' , '/' , 1 , 3) AS sep_3
                 , INSTR(col_a || '/' , '/' , 1 , 4) AS sep_4
                 , INSTR(col_a || '/' , '/' , 1 , 5) AS sep_5
             FROM  data
           ) d
    ;

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I wrote an article on parsing strings in SQL a few years back: Parsing Strings in SQL You can adapt this to put the parsed entries into separate columns instead of rows.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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