Split comma separated entries to rows I have a table like this:

|   ID   |  OtherID  | Data
+--------+-----------+---------------------------
|  5059  |   73831   | 5103,5107
|  5059  |   73941   | 5103,5104,5107
|  5059  |   73974   | 5103,5106,5107,5108


And the result should return individual rows, like this:

|   ID   |  OtherID  | Data
+--------+-----------+--------------------------
|  5059  |   73831   | 5103
|  5059  |   73831   | 5107
|  5059  |   73941   | 5103
|  5059  |   73941   | 5104
|  5059  |   73941   | 5107
|  5059  |   73974   | 5103
|  5059  |   73974   | 5106
|  5059  |   73974   | 5107
|  5059  |   73974   | 5108


Basically, I need to split my data at the comma into individual rows.

The result will be stored in a temporary table (Like: ID, OtherID, NewID).

My version of DB2 is 9.7
I came up with a solution for your data set based on some work that I was doing and some modifications to the solution posted by Serge Rielau and Rick Swagerman on IBM's developerWorks.

Data Setup queries:

DECLARE GLOBAL TEMPORARY TABLE sample_data (id INTEGER, otherid integer, data VARCHAR(255)) WITH REPLACE ON COMMIT preserve rows NOT logged;
INSERT INTO session.sample_data SELECT 5059, 73831, '5103,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73941, '5103,5104,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73974, '5103,5106,5107,5108' FROM sysibm.sysdummy1;


Solution Select Query:

WITH
split_data AS
(
    SELECT
        id as group_by_1,
        otherid as group_by_2,
        data AS split_string,
        ','  AS split
    FROM
        session.sample_data
)
,
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 &lt; 30000
    AND pos &gt; 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;


Results:

ID  OTHERID DATA
5059    73831   5103
5059    73831   5107
5059    73941   5103
5059    73941   5104
5059    73941   5107
5059    73974   5103
5059    73974   5106
5059    73974   5107
5059    73974   5108


Comments:

The solution select query can be modified to fit your specific result needs by including as many GROUP_BY_X rows (O to many) in the REC table definition and matching rows in the two unioned sub-selects.