将列合并为一列-- Oracle PL/SQL

Question

我有下面的脚本和表，在运行脚本时，脚本为LOG_ID、年份、WA.SUB_DIVISION、AI.SUB_DIVISION、EA.SUB_DIVISION、FI.SUB_DIVISION生成输出

是否可以将四列合并为一列( WA.SUB_DIVISION、AI.SUB_DIVISION、EA.SUB_DIVISION、FI.SUB_DIVISION )为单一列( SUB_DIVISION )

不知道该怎么做。

我已经创建了一个sql小提琴示例。

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3c4abb924462dcf5e5f8b0f91019b6b6

       select  distinct  L.LOG_ID, 
        FC.LOG_YR as YEAR, 
        WA.SUB_DIVISION, 
        AI.SUB_DIVISION AS SUB_DIV, 
        EA.SUB_DIVISION AS SUB_DIV3, 
        FI.SUB_DIVISION AS SUB_DIV4
            FROM FINAL_CALENDAR FC
                 JOIN LOG L
                    ON TO_DATE (  TO_CHAR (L.LOG_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') = FC.CAL_DATE

                 LEFT OUTER JOIN LOG_WATER WA
                    ON WA.LOG_ID = L.LOG_ID
                 LEFT OUTER JOIN LOG_AIR AI
                    ON AI.LOG_ID = L.LOG_ID
                  LEFT OUTER JOIN LOG_EARTH EA
                    ON EA.LOG_ID = L.LOG_ID              
                  LEFT OUTER JOIN LOG_FIRE FI
                    ON FI.LOG_ID = L.LOG_ID  

实际输出/发布/现有输出

LOG_ID  YEAR  SUB_DIVISION     SUB_DIV    SUB_DIV3    SUB_DIV4

990741  2020    NULL    NULL    NULL    NULL
990742  2020    NULL    NULL    NULL    NULL    
991122  2020    NULL    NULL    NULL    NULL
991123  2020    NULL    NULL    NULL    NULL
994461  2020    NULL    4       NULL    NULL
994468  2020    NULL    2       NULL    NULL
994466  2020    NULL    2       NULL    NULL
994480  2020    8       NULL    NULL    NULL
994479  2020    8       NULL    NULL    NULL
994476  2020    6       NULL    NULL    NULL
994478  2020    6       NULL    NULL    NULL
994440  2020    NULL    NULL    NULL    NULL
994432  2020    NULL    NULL    NULL    NULL
994450  2020    NULL    NULL    NULL    NULL
994154  2020    NULL    NULL    NULL    NULL

必需/期望输出

LOG_ID   YEAR  SUB_DIVISION     DISPLAY_NAME

990741  2020    NULL            NULL
990742  2020    NULL            NULL   
991122  2020    NULL            NULL
991123  2020    NULL            NULL
994461  2020    4               Triangle       
994468  2020    2               Circle  
994466  2020    2               Circle    
994480  2020    8               Rhombus   
994479  2020    8               Rhombus
994476  2020    6               Dot    
994478  2020    6               Dot
994440  2020    NULL            NULL
994432  2020    NULL            NULL
994450  2020    NULL            NULL
994154  2020    NULL            NULL

表日志；

LOG_ID,         LOG_DATE,           
990741,         to_date('21-JAN-20','DD-MON-RR')
990742                  21-JAN-20                
991122                  24-JAN-20                
991123                  25-JAN-20                
994461                  25-JAN-20
994468                  25-JAN-20
994466                  25-JAN-20
994480                  25-JAN-20
994479                  25-JAN-20
994476                  25-JAN-20
994478                  25-JAN-20
994440                  25-JAN-20
994432                  25-JAN-20
994450                  25-JAN-20
994154                  25-JAN-20

表FINAL_CALENDAR；

CAL_DATE    CAL_MONTH           LOG_YR
21-JAN-20   1                   2020
21-JAN-20   1                   2020
24-JAN-20   1                   2020
25-JAN-20   1                   2020               
25-JAN-20   1                   2020 
25-JAN-20   1                   2020 
25-JAN-20   1                   2020               
25-JAN-20   1                   2020 
25-JAN-20   1                   2020 
25-JAN-20   1                   2020               
25-JAN-20   1                   2020 
25-JAN-20   1                   2020 
25-JAN-20   1                   2020               
25-JAN-20   1                   2020 
25-JAN-20   1                   2020 

表LOG_AIR；

ID      LOG_ID  SUB_DIVISION
134     994468  2
132     994461  4
133     994466  2

表LOG_WATER；

ID      LOG_ID  SUB_DIVISION
9345    994480  8
9344    994479  8
9342    994476  6
9343    994478  6

表LOG_EARTH；

ID      LOG_ID          SUB_DIVISION
0118    994440          null
0117    994432          null

表LOG_FIRE；

ID      LOG_ID      SUB_DIVISION
706     994450      null
705     994154      null

表Z_SUB_DIVISION_TYPE；

SUB_DIVISION    DISPLAY_NAME 
1               Parallelogram   
2               Circle  
3               Square  
4               Triangle    
5               Tangent 
6               Dot 
7               Line    
8               Rhombus 
9               Trapezium   

Answer 1

如果这是最终结果，您可以合并最后四列，假设一列有值，rest为null(细分列)

SELECT log_id,
       year,
       CASE
        WHEN sub_division IS NULL AND SUB_DIV3 IS NULL AND SUB_DIV4 IS NULL THEN sub_div
        WHEN sub_division IS NULL AND SUB_DIV IS NULL AND SUB_DIV4 IS NULL THEN sub_div3
        WHEN sub_division IS NULL AND SUB_DIV IS NULL AND SUB_DIV3 IS NULL THEN sub_div4
        ELSE
        sub_division
       END as sub_division,
       display_name
FROM   (SELECT DISTINCT L.log_id,
                        FC.log_yr                                     AS YEAR,
                        WA.sub_division,
                        AI.sub_division                               AS SUB_DIV,
                        EA.sub_division
                        AS SUB_DIV3,
                        FI.sub_division                               AS SUB_DIV4,
                        (SELECT display_name
                         FROM   z_sub_division_type a
                         WHERE  a.sub_division = WA.sub_division
                         OR     a.sub_division = AI.sub_division
                         OR     a.sub_division = EA.sub_division
                         OR     a.sub_division = FI.sub_division
                         )      AS   DISPLAY_NAME
        FROM   final_calendar FC
               join log L
                 ON To_date (To_char (L.log_date, 'MM/DD/YYYY'), 'MM/DD/YYYY') =
                    FC.cal_date
               left outer join log_water WA
                            ON WA.log_id = L.log_id
               left outer join log_air AI
                            ON AI.log_id = L.log_id
               left outer join log_earth EA
                            ON EA.log_id = L.log_id
               left outer join log_fire FI
                            ON FI.log_id = L.log_id) 

编辑1：-这个sql工作假设至少有一个列有值，rest为null。

编辑2:-您可以用合并替换case子句

coalesce(sub_division,sub_div,sub_div3,sub_div4) as sub_division