我现在数据库中的数据格式是:
X Y
1010 100002945,100900001,100900002,100900003,100900004
1010 990900389,990900398,990900399
我咋样改成以下这种格式:
X Y
1010 100002945
1010 100900001
1010 100900002
1010 100900003
1010 100900004
1010 990900389
1010 990900398
1010 990900399先谢谢啦
X Y
1010 100002945,100900001,100900002,100900003,100900004
1010 990900389,990900398,990900399
我咋样改成以下这种格式:
X Y
1010 100002945
1010 100900001
1010 100900002
1010 100900003
1010 100900004
1010 990900389
1010 990900398
1010 990900399先谢谢啦
SELECT '1010' AA, '100002945,100900001,100900002,100900003,100900004' BB
FROM DUAL
UNION ALL
SELECT '1010', '990900389,990900398,990900399'
FROM DUAL)
SELECT AA,
SUBSTR(',' || BB || ',',
INSTR(',' || BB || ',', ',', 1, XX.RN) + 1,
INSTR(',' || BB || ',', ',', 1, XX.RN + 1) -
INSTR(',' || BB || ',', ',', 1, XX.RN) - 1) BB
FROM TT,
(SELECT ROWNUM RN
FROM dual
CONNECT BY ROWNUM <=
(SELECT MAX(LENGTH(BB) - LENGTH(REPLACE(BB, ',')) + 1) FROM TT)) XX
WHERE INSTR(',' || BB, ',', 1, XX.RN) > 0;