How to Break Down Comma-Separated Columns into Rows Using DB2 SQL?

Breaking Down Comma-Separated Columns into Rows with DB2

In the realm of database management, handling comma-separated values can often pose a challenge, especially when it comes to extracting individual values into separate rows. One such scenario arises when a table contains a column with comma-separated references, and the need arises to split these references into individual rows.

Problem Statement

Consider a table with the following structure:

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

The goal is to transform this data into the following desired output:

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

DB2 Solution

To achieve this data transformation, DB2 provides a recursive SQL statement that iteratively splits the comma-separated values into individual rows. The solution leverages the following SQL code:

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

Explanation

This recursive CTE (Common Table Expression) named unpivot uses the UNION ALL clause to iteratively split the Reference column into individual values:

• lvl represents the recursion level, which ensures that the splitting process terminates after a maximum of 100 levels.
• The first row of the CTE selects the first token from the Reference column as the initial value for reference, and the rest of the string as the tail.
• The subsequent rows use the tail from the previous row as the reference and repeat the splitting process until the tail is empty or the maximum recursion level is reached.
• Finally, the SELECT statement outside the CTE extracts the desired id, fk_ref, and reference columns from the unpivot CTE.

Note:

It's important to avoid storing data in a comma-separated format, as this can lead to data integrity issues and make data manipulation and querying more complex. However, the provided solution offers a way to transform such data into a more manageable and usable format in cases where it's unavoidable or necessary.

The above is the detailed content of How to Break Down Comma-Separated Columns into Rows Using DB2 SQL?. For more information, please follow other related articles on the PHP Chinese website!