Oracle行转列的3种方法

测试表为A3 , 有5个字段：ID1, ID2, ID3, ID4, ID5

测试数据如下：

ID1	ID2	ID3	ID4	ID5
1	2	3	4	5
11	22	33	44	55
111	222	333	444	555

 

结果如下：

ID1	11
ID2	22
ID3	33

 

 方法1：使用系统表

SELECT DECODE(COLUMN_NAME, COLUMN_NAME, COLUMN_NAME) NAME,
DECODE(COLUMN_NAME, 'ID1', ID1, 'ID2', ID2, 'ID3', ID3) VALUE
FROM USER_TAB_COLUMNS U, A3 T
WHERE U.TABLE_NAME = 'A3' AND T.ID5='55'
AND U.COLUMN_NAME IN ('ID1', 'ID2', 'ID3');

 

方法2：使用ROWNUM
SELECT DECODE(N, 1, 'ID1', 2, 'ID2', 3, 'ID3') AS NAME,
DECODE(N, 1, S.ID1, 2, S.ID2, 3, S.ID3) AS VALUE
FROM (SELECT T.* FROM A3 T WHERE T.ID5 = '55') S,
(SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM <= 3);

方法3：使用UNION ALL，最简单的方法
SELECT 'ID1' NAME, ID1 VALUE FROM A3 T WHERE T.ID5='55' UNION ALL
SELECT 'ID2' NAME, ID2 VALUE FROM A3 T WHERE T.ID5='55' UNION ALL
SELECT 'ID3' NAME, ID3 VALUE FROM A3 T WHERE T.ID5='55';