在oracle 9i中将列转换为行[重复]

下面是一种手动取消Pivot的方法:

WITH employees AS (SELECT 1 ID, 0 data1, 1 data2, 3 data3, 2 data4 FROM dual UNION ALL
                   SELECT 2 ID, 1 data1, 0 data2, 3 data3, 0 data4 FROM dual)
SELECT e.ID,
       CASE WHEN d.id = 1 THEN 'DATA 1'
            WHEN d.id = 2 THEN 'DATA 2'
            WHEN d.id = 3 THEN 'DATA 3'
            WHEN d.id = 4 THEN 'DATA 4'
       END SOURCE,
       CASE WHEN d.id = 1 THEN data1
            WHEN d.id = 2 THEN data2
            WHEN d.id = 3 THEN data3
            WHEN d.id = 4 THEN data4
       END DATA
FROM  employees e
      CROSS JOIN (SELECT LEVEL ID
                  FROM   dual
                  CONNECT BY LEVEL <= 4 -- the number of columns to unpivot
                  ) d
WHERE  CASE WHEN d.id = 1 THEN data1
            WHEN d.id = 2 THEN data2
            WHEN d.id = 3 THEN data3
            WHEN d.id = 4 THEN data4
       END > 0
ORDER BY ID,
         DATA;

        ID SOURCE       DATA
---------- ------ ----------
         1 DATA 2          1
         1 DATA 4          2
         1 DATA 3          3
         2 DATA 1          1
         2 DATA 3          3

我在where子句中再次使用了case语句,但您可以在子查询中不使用谓词的情况下进行查询,然后在外部查询中添加过滤器(例如。 select id, source, data from (select e.id, case .... ) where data > 0 )