How to Unpivot Comma-Separated Data in DB2 Using Recursive SQL?

Unpivoting Comma-Separated Data in DB2

In database design, it's unwise to store multiple references in a single column separated by commas. This practice can lead to inefficiencies and data inconsistencies. However, situations may arise where you inherit a database with such data structure, making it necessary to transform it into a normalized form.

To unpivot comma-separated data in DB2, you can leverage recursive SQL. The following query provides a solution:

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 the comma-separated content into individual rows. It employs recursion to iterate through the tail (remaining unprocessed references) and generate new rows as long as there are commas and the level (depth of recursion) is less than 100.

Note: This query has not been tested and may require adjustments to work in your specific environment.

The above is the detailed content of How to Unpivot Comma-Separated Data in DB2 Using Recursive SQL?. For more information, please follow other related articles on the PHP Chinese website!