Something like this:
SELECT
DH.DHid,
DH.A,
DH.B,
DH.C,
IsNull(
(
SELECT TOP 1 DD.Remarks
FROM DataDetail As DD
WHERE DD.DHid = DH.DHid
And DD.Remarks Is Not Null
ORDER BY DD.DDid DESC
),
DH.Remarks
) As Remarks
FROM
DataHeader As DH
;
Or:
WITH cteDetail As
(
SELECT
ROW_NUMBER() OVER (PARTITION BY DHid ORDER BY DDid DESC) As RN,
DHid,
Remarks
FROM
DataDetail
WHERE
Remarks Is Not Null
)
SELECT
DH.DHid,
DH.A,
DH.B,
DH.C,
IsNull(DD.Remarks, DH.Remarks) As Remarks
FROM
DataHeader As DH
LEFT JOIN cteDetail As DD
ON DD.DHid = DH.DHid
And DD.RN = 1
;