Split comma separated entries to rows

Question

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

Answer 1

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 < 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;

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.

Answer 2

A bit late to the party, but I had a recent project where I needed to accomplish something similar with a simpler solution and thought I should share this approach using XMLTABLE instead of recursive SQL.

Data Setup

Data setup is the same as in Jeff Rudnick's answer.

DECLARE GLOBAL TEMPORARY TABLE session.sample_data (
    ID        INTEGER,
    OtherId   INTEGER,
    Data      VARCHAR(255)
    )
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
INSERT INTO session.sample_data values
    (5059, 73831, '5103,5107'),
    (5059, 73941, '5103,5104,5107'),
    (5059, 73974, '5103,5106,5107,5108');

Solution

SELECT sd.ID, sd.OtherId, si.item as NewId
  from session.sample_data sd
      ,XMLTABLE('$doc/items/item'
          PASSING XMLPARSE(DOCUMENT CAST('<items><item><value>'||replace(sd.Data,',','</value></item><item><value>')||'</value></item></items>' as CLOB)) as "doc"
          COLUMNS
          ITEM VARCHAR(255) PATH 'value'
      ) si
;

Results

ID     OTHERID   NEWID
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

Answer 3

You may even shorten the last solution via XMLTABLE() by getting rid of the value tag:

SELECT sd.ID, sd.OtherId, si.item as NewId
  from session.sample_data sd
      ,XMLTABLE('$doc/items/item'
          PASSING XMLPARSE(DOCUMENT CAST(   '<items><item>'
                                         || replace(sd.Data , ',' , '</item><item>')
                                         || '</item></items>' as CLOB
                                        )
                           ) as "doc"
          COLUMNS
          ITEM VARCHAR(255) PATH '.'
      ) si ;