How to Unpivot Comma-Separated Values into Multiple Rows in Db2?

How to Convert Comma-Separated Columns into Multiple Rows in Db2

Many applications require data to be stored in a relational table structure, where each record represents a unique entity and its attributes. However, sometimes data may be structured differently, such as in a comma-separated format within a single column. Transforming such data into a relational structure can be challenging, but Db2 offers a solution using recursive SQL.

Let's consider an example where a table contains a column with comma-separated values:

Id | FK_ID | Reference
-----------------------
1    2100   GI2, GI32
2    2344   GI56

Our goal is to transform this data into a table with multiple rows, where each row represents a distinct reference:

Id | FK_ID | Reference
-----------------------
1    2100   GI2
2    2100   GI32
3    2344   GI56

The recursive SQL query below achieves this transformation:

WITH unpivot (lvl, id, fk_ref, reference, tail) AS (
  SELECT 1, id, fk_ref,
         CASE WHEN LOCATE(',',reference) > 0
              THEN TRIM(LEFT(reference, LOCATE(',',reference)-1))
              ELSE TRIM(reference)
         END,
         CASE WHEN LOCATE(',',reference) > 0
              THEN SUBSTR(reference, LOCATE(',',reference)+1)
              ELSE ''
         END
  FROM yourtable
  UNION ALL
  SELECT lvl + 1, id, fk_ref,
         CASE WHEN LOCATE(',', tail) > 0
              THEN TRIM(LEFT(tail, LOCATE(',', tail)-1))
              ELSE TRIM(tail)
         END,
         CASE WHEN LOCATE(',', tail) > 0
              THEN SUBSTR(tail, LOCATE(',', tail)+1)
              ELSE ''
         END
  FROM unpivot
  WHERE lvl < 100 AND tail != '')
SELECT id, fk_ref, reference FROM unpivot

This query unpivots and iteratively splits the comma-separated values into separate rows. The result is a relational table in the desired format. Note: the query has not been tested and may require minor adjustments.

The above is the detailed content of How to Unpivot Comma-Separated Values into Multiple Rows in Db2?. For more information, please follow other related articles on the PHP Chinese website!