Oracle通用维、父子维相互转换

所谓通用维即维度层级1、2、3均作为字段展示为列，父子维即维度id+父级维度+维度层级字段

通用维

lvl_id1	lvl_name1	lvl_id2	lvl_name2	lvl_id3	lvl_name3

父子维

id	parent_id	id_lvl

 

 

 

oracle通用维转父子维

将顶级机构抽取插入：Insert into dim_org_3 (select distinct org_lvl1_id,org_lvl1_desc,null,null,as_of_date,null from dim_org_lvl where org_lvl1_id is not null)

将2级机构抽取插入：Insert into dim_org_3 (select distinctorg_lvl2_id,org_lvl2_desc,org_lvl1_id,org_lvl1_desc,as_of_date,null from dim_org_lvl where org_lvl2_id is not null)

将3级机构抽取插入：insert into dim_org_3 (select distinctorg_lvl3_id,org_lvl3_desc,org_lvl2_id,org_lvl2_desc,2,as_of_date,null from dim_org_lvl where org_lvl3_id is not null)

oracle父子维转通用维

SELECT A.ORG_ID AS ORG_LVL1_ID,

      A.ORG_NAME AS ORG_LVL1_NAME,

      B.ORG_ID AS ORG_LVL2_ID,

      B.ORG_NAME AS ORG_LVL2_NAME,

      C.ORG_ID AS ORG_LVL3_ID,

      C.ORG_NAME AS ORG_LVL3_NAME,

      D.ORG_ID AS ORG_LVL4_ID,

      D.ORG_NAME AS ORG_LVL4_NAME,

      E.ORG_ID AS ORG_LVL5_ID,

      E.ORG_NAME AS ORG_LVL5_NAME,

      F.ORG_ID AS ORG_LVL6_ID,

      F.ORG_NAME AS ORG_LVL6_NAME,

      NVL(F.ORG_NAME,

          NVL(E.ORG_NAME,

               NVL(D.ORG_NAME, NVL(C.ORG_NAME,NVL(B.ORG_NAME, A.ORG_NAME))))) AS ORG_NAME,

      A.BBQ_||A.USERID_|| NVL(F.ORG_ID,

          NVL(E.ORG_ID,

               NVL(D.ORG_ID, NVL(C.ORG_ID,NVL(B.ORG_ID, A.ORG_ID))))) AS ORG_ID

 FROM DIM_ORG A

 LEFT JOIN DIM_ORG B ON B.UP_ORG_ID = A.ORG_ID

                            AND B.ORG_LEVEL ='1'

 LEFT JOIN DIM_ORG C ON C.UP_ORG_ID = B.ORG_ID

                            AND C.ORG_LEVEL ='2'

 LEFT JOIN DIM_ORG D ON D.UP_ORG_ID = C.ORG_ID

                            AND D.ORG_LEVEL ='3'

 LEFT JOIN DIM_ORG E ON E.UP_ORG_ID = D.ORG_ID

                            AND E.ORG_LEVEL ='4'

 LEFT JOIN DIM_ORG F ON F.UP_ORG_ID = E.ORG_ID

                            AND F.ORG_LEVEL ='5'

 WHERE A.ORG_LEVEL = '0';