Sqlserver- Not Null Cases

saikumar86k 

Hi, Saikumar.

You'll want to leverage UNION for this.

Note, I've deliberately avoided transforming the value 'PRO0003' into 'P03' as I feel that's a mistake. You run the risk that 'P03' already exists and if it does, you then have to think about whether or not 'PRO0003' is logically equivalent to 'P03'.

That said, it's ultimately your decision to make - I just wasn't comfortable with that kind of assumption.

I'm also a little wary of where you've used NULL versus whitespace. I wasn't clear on whether they should be considered logically equivalent, but to start with, I've kept them separate to match your output example.

T-SQL

-- Stage artefacts and data.
DROP TABLE IF EXISTS [foo];

CREATE TABLE [foo]
(
	[id] [int] NOT NULL
	, [r1] [nvarchar](16)
	, [r2] [nvarchar](16)
	, [old_txt] [nchar](1)
	, [new_txt] [nchar](1)
)
GO


INSERT INTO
	[foo]
VALUES
	(11200, '', 'PRO0003', 'A', 'B')
	, (11200, 'P03', 'Q08', 'G', 'D')
	, (11200, 'Q08', 'Q09', 'H', 'E')
;
GO

-- Example query.
SELECT
	[id]
	, [r1]
	, CASE
		WHEN [r2] IS NULL OR [r2] = '' THEN NULL
		ELSE [r2]
	END AS [r2]
	, [old_txt]
	, '' AS [new_txt]
FROM
	[foo]
WHERE
	[foo].[r1] <> ''
	AND [foo].[old_txt] <> ''
UNION ALL
SELECT
	[id]
	, CASE
		WHEN [r1] IS NULL OR [r1] = '' THEN NULL
		ELSE [r1]
	END AS [r1]
	, [r2]
	, '' AS [old_txt]
	, [new_txt]
FROM
	[foo]
WHERE
	[foo].[r2] <> ''
	AND [foo].[new_txt] <> ''
ORDER BY
	[id]
	, [r1]
	, [r2]
;

Output

Cheers,

Lain