Oracle 11g desired output from below table-CodePudding

I have below the table in my oracle 11g XE database.

COL1            COL2        COL3       COL4         COL5 COL6   COL7  COL8                                                  
Legacy ID       1234                                        
9/1/2022                   25035389    MT           REC  DEU    BGD   123.76
                                       0.558
9/1/2022                   25035390    MT           REC  DEU    BGD   123.76
                                       0.558
9/1/2022                   25035391    MT           REC  DEU    BGD   123.76
                                       0.558

My Desired Output should be like below table:

COL1            COL2            COL3       COL4    COL5  COL6   COL7 COL8                                                   
Legacy ID       1234                                    
9/1/2022        1234           25035389    MT      REC   DEU    BGD  123.76
9/1/2022        1234           25035389            0.558
9/1/2022        1234           25035390    MT      REC   DEU    BGD  123.76
9/1/2022        1234           25035390            0.558
9/1/2022        1234           25035391    MT      REC   DEU    BGD  123.76
9/1/2022        1234           25035391            0.558

How can I get desired output by sql

CodePudding user response：

With your sample data:

WITH
    tbl AS
        (
            Select 'Legacy ID' "COL1", '1234' "COL2", Null     "COL3", Null    "COL4", Null  "COL5", Null  "COL6", Null  "COL7", Null     "COL8" From Dual Union All
            Select '9/1/2022'  "COL1", Null   "COL2", 25035389 "COL3", 'MT'    "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
            Select Null        "COL1", Null   "COL2", Null     "COL3", '0.558' "COL4", Null  "COL5", Null  "COL6", Null  "COL7", Null     "COL8" From Dual Union All
            Select '9/1/2022'  "COL1", Null   "COL2", 25035390 "COL3", 'MT'    "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
            Select Null        "COL1", Null   "COL2", Null     "COL3", '0.558' "COL4", Null  "COL5", Null  "COL6", Null  "COL7", Null     "COL8" From Dual Union All
            Select '9/1/2022'  "COL1", Null   "COL2", 25035391 "COL3", 'MT'    "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
            Select Null        "COL1", Null   "COL2", Null     "COL3", '0.558' "COL4", Null  "COL5", Null  "COL6", Null  "COL7", Null     "COL8" From Dual
        )
--  Sample data
--  COL1      COL2       COL3 COL4  COL5 COL6 COL7 COL8 
--  --------- ---- ---------- ----- ---- ---- ---- ------
--  Legacy ID 1234                                        
--  9/1/2022         25035389 MT    REC  DEU  BGD  123.76 
--                            0.558                       
--  9/1/2022         25035390 MT    REC  DEU  BGD  123.76 
--                            0.558                       
--  9/1/2022         25035391 MT    REC  DEU  BGD  123.76 
--                            0.558

Try it like this:

SELECT
    CASE WHEN COL1 Is NULL THEN Min(COL1) OVER(Partition By 1 Order By 1 Rows Between UNBOUNDED PRECEDING AND CURRENT ROW) ELSE COL1 END "COL1",
    CASE WHEN COL2 Is NULL THEN First_Value(COL2) OVER(Partition By 1 Order By 1 Rows Between UNBOUNDED PRECEDING AND CURRENT ROW) ELSE COL2 END "COL2",
    CASE WHEN COL3 Is NULL THEN Max(COL3) OVER(Partition By 1 Order By 1 Rows Between 1 PRECEDING And CURRENT ROW) ELSE COL3 END "COL3",
    CASE WHEN COL4 = 'MT' THEN COL4 END "COL4",
    CASE WHEN COL4 = 'MT' THEN COL5 ELSE COL4 END "COL5",
    COL6 "COL6", 
    COL7 "COL7",
    COL8 "COL8"
FROM
    tbl

...

--  R e s u l t :
--  COL1      COL2       COL3 COL4  COL5  COL6 COL7 COL8 
--  ------- ---- ---------- ----- ----- ---- ---- ------
--  Legacy ID 1234                                         
--  9/1/2022  1234   25035389 MT    REC   DEU  BGD  123.76 
--  9/1/2022  1234   25035389       0.558                  
--  9/1/2022  1234   25035390 MT    REC   DEU  BGD  123.76 
--  9/1/2022  1234   25035390       0.558                  
--  9/1/2022  1234   25035391 MT    REC   DEU  BGD  123.76 
--  9/1/2022  1234   25035391       0.558