我有下表(引用的数量是可变的):
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转换数据的简短方法?
解决方法@H_301_12@
你真的不应该存储这样的数据.幸运的是,有一种方法可以通过递归sql来消除损坏,具体如下:
WITH unpivot (lvl,id,fk_ref,reference,tail) AS (
SELECT 1,CASE WHEN LOCATE(',',reference) > 0
THEN TRIM(LEFT(reference,LOCATE(',reference)-1))
ELSE TRIM(reference)
END,reference) > 0
THEN SUBSTR(reference,reference)+1)
ELSE ''
END
FROM yourtable
UNION ALL
SELECT lvl + 1,tail) > 0
THEN TRIM(LEFT(tail,tail)-1))
ELSE TRIM(tail)
END,tail) > 0
THEN SUBSTR(tail,tail)+1)
ELSE ''
END
FROM unpivot
WHERE lvl < 100 AND tail != '')
SELECT id,reference FROM unpivot
PS.未经测试.
WITH unpivot (lvl,id,fk_ref,reference,tail) AS ( SELECT 1,CASE WHEN LOCATE(',',reference) > 0 THEN TRIM(LEFT(reference,LOCATE(',reference)-1)) ELSE TRIM(reference) END,reference) > 0 THEN SUBSTR(reference,reference)+1) ELSE '' END FROM yourtable UNION ALL SELECT lvl + 1,tail) > 0 THEN TRIM(LEFT(tail,tail)-1)) ELSE TRIM(tail) END,tail) > 0 THEN SUBSTR(tail,tail)+1) ELSE '' END FROM unpivot WHERE lvl < 100 AND tail != '') SELECT id,reference FROM unpivot
PS.未经测试.