Home >Database >Mysql Tutorial >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!