8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

如何将 UNPIVOT 拆分成多个列?

onlyf 2月前

39 0

我有一张类似下图的表格,我需要类似下图的输出,我有一个使用 union 的查询来获取所需的输出,选择 different cd_5 作为 ORG_UNIT_CD,upper('\''||nm_5||'\'') 作为 ORG_U...

我有一张如下图所示的表格

我需要像下图这样的输出

enter image description here

我有一个使用 union 来获取所需输出的查询

    select distinct cd_5 as ORG_UNIT_CD, upper('"'||nm_5||'"') as ORG_UNIT_NM, cd4 as PARENT_ORG_UNIT_CD, 5 as ORG_UNIT_LEVEL 
from TBL
where cd_5 <> 0
union
select distinct cd_4 as ORG_UNIT_CD, upper('"'||nm_4||'"') as ORG_UNIT_NM, cd_3 as PARENT_ORG_UNIT_CD, 4 as ORG_UNIT_LEVEL 
from TBL 
where cd_4 <> 0
union
select distinct cd_3 as ORG_UNIT_CD, upper('"'||nm_3||'"') as ORG_UNIT_NM, cd_2 as PARENT_ORG_UNIT_CD, 3 as ORG_UNIT_LEVEL 
from TBL 
where cd_3 <> 0
union
select distinct cd_2 as ORG_UNIT_CD, upper('"'||nm_2||'"') as ORG_UNIT_NM, cd_1 as PARENT_ORG_UNIT_CD, 2 as ORG_UNIT_LEVEL 
from TBL
where cd_2 <> 0
union
select distinct cd_1 as ORG_UNIT_CD, upper('"'||nm_1||'"') as ORG_UNIT_NM, 0 as PARENT_ORG_UNIT_CD, 1 as ORG_UNIT_LEVEL 
from TBL 
where cd_1 <> 0
order by ORG_UNIT_LEVEL

我需要一个简化的查询来代替这个。因为我只给出了五个级别的层次结构,但实际上我有 20 个级别。

帖子版权声明 1、本帖标题:如何将 UNPIVOT 拆分成多个列?
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由onlyf在本站《oracle》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 以下是适用于您的数据的 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.
    
  • 做得好!!!这就​​是我要找的。它适用于表格。但我的源是一个视图,我在使用该视图作为源时收到错误。需要针对该视图执行任何操作吗???

  • Matt 2月前 0 只看Ta
    引用 4

    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)
    
返回
作者最近主题: