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
UPDATED ANSWER
With your changed 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 Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "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 Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual Union All
Select 'Legacy ID' "COL1", '4567' "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 Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual Union All
Select 'Legacy ID' "COL1", '6789' "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 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 Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual
),
/*
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
--------- ---- ---------- ----- ---- ---- ---- ------
Legacy ID 1234
9/1/2022 25035389 MT REC DEU BGD 123.76
0.558
0.558
0.558
0.558
Legacy ID 4567 0.558
9/1/2022 25035390 MT REC DEU BGD 123.76
0.558
0.558
Legacy ID 6789 0.558
9/1/2022 25035391 MT REC DEU BGD 123.76
0.558
0.558
0.558
*/
Create a CTE to get you row span for COL2 value
tbl_2 AS
( SELECT
COL2, RN_VAL,
CASE WHEN Max(RN_VAL) OVER(Partition By 1 Order By RN_VAL ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) = RN_VAL THEN 999999999
ELSE Max(RN_VAL) OVER(Partition By 1 Order By RN_VAL ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) - 1
END "SPAN"
FROM
( Select
COL2 "COL2",
Nvl(Last_Value(COL2) OVER(Order By ROWNUM Rows Between Current Row And 1 Following), First_Value(COL2) OVER(Order By ROWNUM Rows Between Unbounded Preceding And Current Row)) "NEXT_VAL",
CASE WHEN COL2 Is Not Null THEN ROWNUM END "RN_VAL"
From
tbl
)
WHERE RN_VAL Is Not Null
)
Main sql
SELECT
t1.COL1, t2.COL2, t1.COL3, t1.COL4, t1.COL5, t1.COL6, t1.COL7, t1.COL8
FROM
( SELECT
ROWNUM "RN",
CASE WHEN t.COL1 Is NULL THEN Min(t.COL1) OVER(Partition By 1 Order By 1 Rows Between UNBOUNDED PRECEDING AND CURRENT ROW) ELSE t.COL1 END "COL1",
t.COL3 "COL3",
CASE WHEN t.COL4 = 'MT' THEN t.COL4 END "COL4",
CASE WHEN t.COL4 = 'MT' THEN t.COL5 ELSE t.COL4 END "COL5",
t.COL6 "COL6",
t.COL7 "COL7",
t.COL8 "COL8"
FROM
tbl t
) t1
INNER JOIN
tbl_2 t2 ON(t1.RN Between t2.RN_VAL And t2.SPAN )
/* 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 0.558
9/1/2022 1234 0.558
9/1/2022 1234 0.558
9/1/2022 1234 0.558
Legacy ID 4567 0.558
9/1/2022 4567 25035390 MT REC DEU BGD 123.76
9/1/2022 4567 0.558
9/1/2022 4567 0.558
Legacy ID 6789 0.558
9/1/2022 6789 25035391 MT REC DEU BGD 123.76
9/1/2022 6789 0.558
9/1/2022 6789 0.558
9/1/2022 6789 0.558
*/