Sqlserver- Not Null Cases

saikumar86k 

Since there is a correct answer. I have a method isn't much rigorous, but looks simple😁

Base your logic, we can say that when R2 is not null, new_txt must be shown, This is clear, because whether or not R1 has value, if new_txt be shown is only depend on if R2 is null.

And when R1 is not null, old_txt must be shown. This is not much rigorous, base on your logic, it looks right. But, there are other conditions exist:

R1 is not null and R2 is null;

R1 is null and R2 is null.

Since you were not mention those, I can think they are not exists in your data.😆

So I can write SQL this way

CREATE TABLE #T
(
	 [ID] [int] NOT NULL
	,[R1] [varchar](10)
	,[R2] [varchar](10)
	,[Old_txt] [varchar](10)
	,[New_txt] [varchar](10)
)
GO

INSERT INTO #T
VALUES
	 (11200, NULL, 'PRO0003', 'A', 'B')
	,(11200, 'P03', 'Q08', 'G', 'D')
	,(11200, 'Q08', 'Q09', 'H', 'E')
GO

SELECT ID, R1, R2, '' as Old_text, New_txt FROM #T WHERE R2 is not null
UNION ALL
SELECT ID, R1, R2, Old_txt, '' as New_txt FROM #T where R1 is not null

DROP TABLE  #T

Result is

ID          R1         R2         Old_text   New_txt
----------- ---------- ---------- ---------- ----------
11200       NULL       PRO0003               B
11200       P03        Q08                   D
11200       Q08        Q09                   E
11200       P03        Q08        G          
11200       Q08        Q09        H