Select info from row to another row

I have this select on a view:

SELECT
    T1.ID AS [ID],
    T1.A AS [A],
    T1.B AS [B],
    T4.C AS [C],
    ISNULL(NULLIF(T3.D, ''), T2.D) AS [D],
    T1.E AS [E],
    T1.F AS [F],
    T1.G AS [G]
FROM T1
INNER JOIN T2 ON T2.X = T1.X
INNER JOIN T3 ON T3.Y = T2.Y
RIGHT JOIN T4 ON T4.Z = T3.Z

And get this:

║  ID  ║  A   ║  B   ║  C   ║  D   ║  E   ║  F   ║  G   ║  
╠══════╬══════╬══════╬══════╬══════╬══════╬══════╬══════╬
║ 1    ║ 3    ║ 4    ║ 1000 ║ X    ║ 1    ║ 1    ║ 1    ║ 
║ 1    ║ 3    ║ 4    ║ 2000 ║ Y    ║ 1    ║ 1    ║ 1    ║ 
║ NULL ║ NULL ║ NULL ║ 3000 ║ NULL ║ NULL ║ NULL ║ NULL ║ 

And I want that the last row be like this:

║ 1 ║ 3 ║ 4 ║ 3000 ║ Z ║ 1 ║ 1 ║ 1 ║

That is, the values of all columns equal to the other rows, except column ‘C’ and column ‘D’. The value of column D is obtained from T2.D

How can I do? Thanks.

Answer

Without any sample data to use I would suggest lag() may be of use in this exact scenario, such as

select
    IsNull(T1.ID, Lag(T1.ID) over(order by T4.C)) as [ID],
    IsNull(T1.A, Lag(T1.A) over(order by T4.C))) as [A],
    IsNull(T1.B, Lag(T1.B) over(order by T4.C))) as [B],
    T4.C as [C],
    IsNull(NullIf(T3.D, ''), T2.D) as [D],
    IsNull(T1.E, Lag(T1.E) over(order by T4.C))) as [E],
    IsNull(T1.F, Lag(T1.F) over(order by T4.C))) as [F],
    IsNull(T1.G, Lag(T1.G) over(order by T4.C))) as [G],
from T1
inner join T2 on T2.X = T1.X
inner join T3 on T3.Y = T2.Y
right join T4 on T4.Z = T3.Z