oracle 行转列问题

[低调灬小白]

现有表数据
a 1,2,3
b 4,5
sql查询结果为
a 1
a 2
a 3
b 4
b 5
请问sql应该怎么写

[yaiger]

SELECT columnA, REGEXP_SUBSTR(columnB, '[^,]+', 1, lv) mat
FROM table, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 10) b
WHERE b.lv <= REGEXP_COUNT(columnB, '\,') + 1
ORDER BY columnA;