如何将逗号分隔的列转换为db2中的多行 [英] how to transform comma separated column into multiples rows in db2
本文介绍了如何将逗号分隔的列转换为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屋!
查看全文