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
-----------------------
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 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;
select id,a2 from test union
select id,a3 from test)
where a is not null;
WITH TEMP AS (
SELECT '01' AS "id",'r1' AS a1 ,'r2' AS a2,'r3' AS a3 FROM DUAL UNION ALL
SELECT '02','r1','','r3' FROM DUAL UNION ALL
SELECT '03','r1','r2','' FROM DUAL
)
SELECT * FROM(
SELECT T."id",
(CASE WHEN P.num=1 THEN T.a1
WHEN P.num=2 THEN T.a2
WHEN P.num=3 THEN T.a3
END ) "a"
FROM (SELECT LEVEL num FROM DUAL CONNECT BY LEVEL<=3)P
CROSS JOIN TEMP T
)
WHERE "a" IS NOT NULL
----利用CONNECT BY 产生的序列数与TEMP表进行交叉连接,
----可以实现COLUMN TO ROW的功能 id a
-- --
01 r1
02 r1
03 r1
01 r2
03 r2
01 r3
02 r3 7 rows selected