id a1 a2 a3
-----------------------
01 r1 r2 r3
02 r1 r3
03 r1 r2
如何将上面的表拆分成下面的样子：
id a
--------------
01 r1
01 r2
01 r3
02 r1
02 r3
03 r1
03 r2
------解决方案--------------------
select * from (
select id, decode(lvl, 1, a1, 2, a2, 3, a3) as a
from t, (select level lvl from dual connect by level <= 3)
) where a is not null order by id, a;