Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Unanswered: CSV data in column into rows

    I have a table like this:

    ID Values
    1 1234,2345,3456
    2 4321,5432,6543

    I need the results in this format in a simple SQL, I am using DB2 V9.7

    ID Values
    1 1234
    1 2345
    1 3456
    2 4321
    2 5432

    Thank you for your support.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:

    Code:
    select t.id, x.token
    from table (values 
      (1, '1234,2345,3456')
    , (2, '4321,5432,6543')
    ) t(id, values)
    , xmltable('for $t in tokenize($s, ",") return <i>{string($t)}</i>' passing t.values as "s"
    columns 
      token varchar(10) path '.'
    ) x
    
    ID          TOKEN     
    ----------- ----------
              1 1234      
              1 2345      
              1 3456      
              2 4321      
              2 5432      
              2 6543      
    
      6 record(s) selected.
    Regards,
    Mark.

  4. #4
    Join Date
    Oct 2002
    Posts
    15
    Mark, I tried to use an edited version of your response to solve a problem I had with this as it seems an elegant solution but I keep getting numerous errors.

    I am trying to replace manually entering the string information with specific data from a table, being INTEGER, and VARCHAR(250) fields from a single table for the ID and values columns respectively. The 'delimiter' in the varchar field in my case is a "+".

    I have tried two versions but both are failing:

    select B.BODY_ID, x.token
    from BODY_TABLE
    B(BODY_ID, BODY_TXT)
    , xmltable('for $B in tokenize($s, "+") return <i>{string($B)}</i>' passing B.BODY_TXT as "s"
    columns
    token varchar(10) path '.'
    ) x
    WHERE B.BODY_ID = 1;

    or this

    select t.id, x.token
    from table (SELECT BODY_ID, BODY_TXT FROM BODY_TABLE WHERE BODY_ID = 1
    ) t(id, values)
    , xmltable('for $t in tokenize($s, "+") return <i>{string($t)}</i>' passing t.values as "s"
    columns
    token varchar(10) path '.'
    ) x;

    Any ideas how I can fix either of these ?
    Thanks, Fin.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Fin,

    You have to escape a "+" character since the 2-nd parameter of the tokenize function is a regular expression.
    Try this:
    Code:
    select B.BODY_ID, x.token
    from BODY_TABLE B
    , xmltable('for $B in tokenize($s, "\+") return <i>{string($B)}</i>' passing B.BODY_TXT as "s"
    columns
    token varchar(10) path '.'
    ) x
    WHERE B.BODY_ID = 1;
    Regards,
    Mark.

Posting Permissions

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