如何在db2中将逗号分隔的列转换为多行 [英] how to transform comma separated column into multiples rows in db2

问题描述

我有下表(引用的数量是可变的):

I have the following table (the number of the references is variable):

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

我需要以下结果:

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

有没有什么捷径可以像这样使用 DB2 转换数据?

Is there any short way to transform the data like this using DB2?

推荐答案

你真的不应该这样存储数据.幸运的是，有一种方法可以通过递归 SQL 来消除损坏，大致如下:

You really should not be storing data like this. Fortunately, there is a way to undo the damage with recursive SQL, something along these lines:

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

PS.未测试.

这篇关于如何在db2中将逗号分隔的列转换为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！