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

Unpivoting Comma-Separated Columns in DB2

Data storage with comma-separated columns can pose challenges. This query demonstrates how to transform such data in DB2 using recursive SQL techniques.

Consider the initial table:

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

The desired result is to separate the comma-separated values into multiple rows:

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

Using recursive SQL, we can achieve 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

Note: This solution assumes the number of comma-separated values does not exceed 99. Adjusting the lvl condition can increase the supported values.

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