以下是适用于您的数据的 UNPIVOT（适用于 Oracle 11+ 版本）示例：

DROP TABLE mytable;
CREATE TABLE mytable(
   OBJECT_ID INTEGER  NOT NULL PRIMARY KEY 
  ,CD_1      INTEGER  NOT NULL
  ,NM_1      VARCHAR(15) NOT NULL
  ,CD_2      INTEGER  NOT NULL
  ,NM_2      VARCHAR(28) NOT NULL
  ,CD_3      INTEGER  NOT NULL
  ,NM_3      VARCHAR(20) NOT NULL
  ,CD_4      INTEGER  NOT NULL
  ,NM_4      VARCHAR(28) NOT NULL
  ,CD_5      INTEGER  NOT NULL
  ,NM_5      VARCHAR(16) NOT NULL
  ,DT        VARCHAR(14) NOT NULL
);


insert into mytable
SELECT 53027429 AS OBJECT_ID,53001326 AS CD_1,'CEO & President' AS NM_1,53000330 AS CD_2,'Merck Manufacturing Division' AS NM_2,53011048 AS CD_3,'EMEA Operations' AS NM_3,54001626 AS CD_4,'HSC PLANT DIRECTION' AS NM_4,53027385 AS CD_5,'HSC NOT STERILE' AS NM_5,'4/24/2013 8:22' AS DT from dual
UNION ALL
SELECT 54010819,53001326,'CEO & President',54002711,'Merck Consumer Care',54007326,'MCC Emerging Markets',54010308,'Consumer Care - Asia Pacific',54010819,'MCC Singapore','8/23/201211:26' from dual
UNION ALL
SELECT 53025491,53001326,'CEO & President',53000330,'Merck Manufacturing Division',53011048,'EMEA Operations',54003518,'Rathdrum - Plant Manager',53020040,'Engineering & FM','5/22/2012 6:24' from dual
UNION ALL
SELECT 53023206,53001326,'CEO & President',53000321,'Corporate Finance',53000333,'MMD Finance',54001411,'Operations',54004358,'MMD Finance EMEA','5/31/2012 6:51' from dual
UNION ALL
SELECT 53021255,53001326,'CEO & President',53008586,'Global Human Health',54001286,'EUCan1',53013126,'Mid-Europe 1',53019000,'MER-1 Balkans','3/14/2013 7:06' from dual;

commit;


select distinct cd, nm, 
  case when parent=cd then 0 else parent end as parent, lvl 
from (
  select * from mytable
  unpivot (
      (cd, nm, parent) for lvl in ((CD_1, NM_1, CD_1) as 1,(CD_2, NM_2, CD_1) as 2,(CD_3, NM_3, CD_2) as 3,(CD_4, NM_4, CD_3) as 4, (CD_5, NM_5, CD_4) as 5)
  )
)
order by lvl;

输出：

        CD NM                               PARENT        LVL
---------- ---------------------------- ---------- ----------
  53001326 CEO & President                       0          1
  53000321 Corporate Finance              53001326          2
  53008586 Global Human Health            53001326          2
  54002711 Merck Consumer Care            53001326          2
  53000330 Merck Manufacturing Division   53001326          2
  53011048 EMEA Operations                53000330          3
  54001286 EUCan1                         53008586          3
  54007326 MCC Emerging Markets           54002711          3
  53000333 MMD Finance                    53000321          3
  54010308 Consumer Care - Asia Pacific   54007326          4
  54001626 HSC PLANT DIRECTION            53011048          4
  53013126 Mid-Europe 1                   54001286          4
  54001411 Operations                     53000333          4
  54003518 Rathdrum - Plant Manager       53011048          4
  53020040 Engineering & FM               54003518          5
  53027385 HSC NOT STERILE                54001626          5
  54010819 MCC Singapore                  54010308          5
  53019000 MER-1 Balkans                  53013126          5
  54004358 MMD Finance EMEA               54001411          5

19 rows selected.

做得好！！！这就​​是我要找的。它适用于表格。但我的源是一个视图，我在使用该视图作为源时收到错误。需要针对该视图执行任何操作吗？？？

ORA-00933：SQL 命令未正确结束 原因：SQL 语句以不适当的子句结尾。例如，CREATE VIEW 或 INSERT 语句中可能包含 ORDER BY 子句。ORDER BY 不能用于创建有序视图或按特定顺序插入。 操作：通过删除不适当的子句来更正语法。可能会将删除的子句与另一个 SQL 语句重复。例如，要对视图的行进行排序，请在查询视图时执行此操作，而不是在创建视图时执行此操作。

@sureshhh，你最好打开/提出一个新问题，因为很难说出你的查询有什么问题，因为你看不到它。

在 Oracle-11g 中使用 Unpivot ：

它执行列到行的操作，并使用解码来导出其他列的值。

尝试一下：

select distinct /*product_code,*/quantity as ORG_UNIT_CD,
decode(product_code,'cd1','"'||nm1||'"','cd2','"'||nm2||'"','cd3','"'||nm3||'"','cd4','"'||nm4||'"','cd5','"'||nm5||'"') as ORG_UNIT_NM,
decode(product_code,'cd1','0','cd2','cd1','cd3','cd2','cd4','cd3','cd5','cd4') as PARENT_ORG_UNIT_CD
from unpivot_test2
UNPIVOT(quantity FOR product_code in (cd1 as 'cd1',cd2 as 'cd2',cd3 as 'cd3', cd4 as 'cd4', cd5 as 'cd5'))
where quantity <> 0;;

我怀疑你正在追求以下类似的东西：

with dummy as (select 1 id
               from   dual
               connect by level <= 5)
select case when d.id = 1 then t.cd_1
            when d.id = 2 then t.cd_2
            when d.id = 3 then t.cd_3
            when d.id = 4 then t.cd_4
            when d.id = 5 then t.cd_5
       end org_unit_cd,
       case when d.id = 1 then t.nm_1
            when d.id = 2 then t.nm_2
            when d.id = 3 then t.nm_3
            when d.id = 4 then t.nm_4
            when d.id = 5 then t.nm_5
       end org_unit_nm,
       case when d.id = 1 then 0
            when d.id = 2 then t.cd_1
            when d.id = 3 then t.cd_2
            when d.id = 4 then t.cd_3
            when d.id = 5 then t.cd_4
       end parent_org_unit_cd,
       d.id org_unit_level
from   (select distinct cd_1,
                        cd_2,
                        cd_3,
                        cd_4,
                        cd_5,
                        '"'||upper(nm_1)||'"' nm_1,
                        '"'||upper(nm_2)||'"' nm_2,
                        '"'||upper(nm_3)||'"' nm_3,
                        '"'||upper(nm_4)||'"' nm_4,
                        '"'||upper(nm_5)||'"' nm_5
        from   tbl) t
       cross join dummy d
where  case when d.id = 1 then t.cd_1
            when d.id = 2 then t.cd_2
            when d.id = 3 then t.cd_3
            when d.id = 4 then t.cd_4
            when d.id = 5 then t.cd_5
       end != 0;

这只是将 TBL 中的每个不同行复制 5 次，并在相关行上输出每个 cd_N、nm_N 和 cd_n-1。以上操作在 10g 和 11g 中均有效。

以下答案在 11g 中有效：

select org_unit_cd,
       org_unit_nm,
       parent_org_unit_cd,
       org_unit_level
from   (select distinct cd_1,
                        cd_2,
                        cd_3,
                        cd_4,
                        cd_5,
                        '"'||upper(nm_1)||'"' nm_1,
                        '"'||upper(nm_2)||'"' nm_2,
                        '"'||upper(nm_3)||'"' nm_3,
                        '"'||upper(nm_4)||'"' nm_4,
                        '"'||upper(nm_5)||'"' nm_5,
                        0 cd_0
        from   tbl)
unpivot ((org_unit_cd, org_unit_nm, parent_org_unit_cd) for org_unit_level in ((cd_1, nm_1, cd_0) as 1,
                                                                               (cd_2, nm_2, cd_1) as 2,
                                                                               (cd_3, nm_3, cd_2) as 3,
                                                                               (cd_4, nm_4, cd_3) as 4,
                                                                               (cd_5, nm_5, cd_4) as 5))

Aman 抢在我之前完成了反透视，所以我给他投了赞成票，但这里有一个例子，你可以直接运行它来查看结果

WITH sample_data
     AS (SELECT 'Smith' family_name,
                'Pappy Smith' Great_Grandparent,
                'Fred Smith' GrandParent,
                'Joey Smith' Parent,
                'Joey Jr' Child
           FROM DUAL
         UNION ALL
         SELECT 'Jones',
                'Great Grammy Jones',
                'Grammy Jones',
                'Bob',
                'Bobby'
           FROM DUAL)
  SELECT family_name, source, val
    FROM sample_data UNPIVOT INCLUDE NULLS (val
           FOR (source)
           IN  (Great_Grandparent AS 'Great_Grandparent',
               Grandparent AS 'Granparent',
               Parent AS 'Parent',
               Child AS 'Child') )
ORDER BY family_name,
         DECODE (source,
                 'Great_Grandparent', 1,
                 'Granparent', 2,
                 'Parent', 3,
                 4)