Results 1 to 4 of 4
  1. #1
    Join Date
    May 2017
    Posts
    2

    Question Answered: DB2 SQL transpose by cell content

    Dear DB2 experts,

    We got cell content that looks like that 432/211/444. There are codes delimited by /
    The number of codes varies.
    A typical data row might look like that

    Type__ID__Code
    Mouse_33__211/332/999
    Cow___12__333/111
    Lion__93__111/233/211/999

    The aim is to transpose each code into a row.

    Type__ID__Code
    Mouse_33__211
    Mouse_33__332
    Mouse_33__999
    Cow___12__333
    Cow___12__111
    Lion__93__111
    Lion__93__233
    Lion__93__211
    Lion__93__999

    What is the best way to achieve that. Can I split the string? Do I have to split and loop through the items?

    Many thanks for any hints.

  2. Best Answer
    Posted by mark.bb

    "Try this:

    Code:
    create function regexp_replace(
      source varchar(4000)
    , pattern varchar(128)
    , replacement varchar(128)
    , flags varchar(4)
    )
    deterministic
    contains sql
    no external action
    returns varchar(4000)
    return xmlcast(
    xmlquery('fn:replace($s, $p, $r, $f)' passing 
      source as "s"
    , pattern as "p"
    , replacement as "r"
    , flags as "f"
    )
    as varchar(4000));
    
    create function regexp_tokenize(
      source clob(2M)
    , pattern varchar(128))
    returns table (seq int, tok varchar(4000))
    contains sql
    deterministic
    no external action
    return
    select seq, tok
    from xmltable('for $id in tokenize($s, $p) return <i>{string($id)}</i>' 
    passing 
      source as "s"
    , pattern as "p"
    columns 
      seq for ordinality
    , tok varchar(4000) path '.'
    ) t;
    
    select regexp_replace (tab.Type__ID__Code, '(.*__).*', '$1', '')||tok.tok as Type__ID__Code
    from table(values
      'Mouse_33__211/332/999'
    , 'Cow___12__333/111'
    , 'Lion__93__111/233/211/999'
    ) tab(Type__ID__Code)
    , table (regexp_tokenize(regexp_replace (tab.Type__ID__Code, '.*__', '', ''), '/')) tok;
    "


  3. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    if there is always a slash, try googling for comma separated field. There was a thread on here in the couple of years that did the same for comma separated list, using POSSTR, I believe.

  4. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Try this:

    Code:
    create function regexp_replace(
      source varchar(4000)
    , pattern varchar(128)
    , replacement varchar(128)
    , flags varchar(4)
    )
    deterministic
    contains sql
    no external action
    returns varchar(4000)
    return xmlcast(
    xmlquery('fn:replace($s, $p, $r, $f)' passing 
      source as "s"
    , pattern as "p"
    , replacement as "r"
    , flags as "f"
    )
    as varchar(4000));
    
    create function regexp_tokenize(
      source clob(2M)
    , pattern varchar(128))
    returns table (seq int, tok varchar(4000))
    contains sql
    deterministic
    no external action
    return
    select seq, tok
    from xmltable('for $id in tokenize($s, $p) return <i>{string($id)}</i>' 
    passing 
      source as "s"
    , pattern as "p"
    columns 
      seq for ordinality
    , tok varchar(4000) path '.'
    ) t;
    
    select regexp_replace (tab.Type__ID__Code, '(.*__).*', '$1', '')||tok.tok as Type__ID__Code
    from table(values
      'Mouse_33__211/332/999'
    , 'Cow___12__333/111'
    , 'Lion__93__111/233/211/999'
    ) tab(Type__ID__Code)
    , table (regexp_tokenize(regexp_replace (tab.Type__ID__Code, '.*__', '', ''), '/')) tok;
    Regards,
    Mark.

  5. #4
    Join Date
    May 2017
    Posts
    2

    Lightbulb simpler sql

    Hi Mark,
    Thanks for your reply. I found this simpler looking solution:

    Code:
    WITH
    split_data AS
    (
        SELECT NUMMER1 as group_by_1,
               NUMMER2 as group_by_2,
               ERROCODES AS split_string,
               '/'  AS split
        FROM TEST1.TRANSPOSETEST
    )
    ,
    rec
    (   group_by_1,
        group_by_2,
        split_string,
        split,
        row_num,
        column_value,
        pos
    ) AS
    (
        SELECT group_by_1,
               group_by_2,
               split_string,
               split,
               1,
               VARCHAR(SUBSTR(split_string, 1, DECODE(INSTR(split_string, split, 1), 0, LENGTH(split_string), INSTR(split_string, split, 1) - 1)), 255),
               INSTR(split_string, split, 1) + LENGTH(split)
        FROM split_data
        UNION ALL
        SELECT
            group_by_1,
            group_by_2,
            split_string,
            split,
            row_num + 1,
            VARCHAR(SUBSTR(split_string, pos, DECODE(INSTR(split_string, split, pos), 0, LENGTH(split_string) - pos + 1, INSTR(split_string, split, pos) - pos)), 255),
            INSTR(split_string, split, pos) + LENGTH(split)
        FROM rec WHERE row_num < 30000 AND pos > LENGTH(split)
    )
    SELECT
        group_by_1 as id,
        group_by_2 as otherid,
        column_value AS data
    FROM rec
    ORDER BY group_by_1,group_by_2,row_num;
    That I applied from https://dba.stackexchange.com/questi...ntries-to-rows

    I wonder what it does. I dont really understand it.

Tags for this Thread

Posting Permissions

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