Sqlserver- Not Null Cases

[saikumar86k]

Hi, 

Please help me on below requirement.

In my table, we have 5 columns, and ID is the key column.

And I need a logic like

1. If Reason1 is null & Reason2 is not null then New_Txt

2. If Reason1 is not null and Reason2 is not null then Old_txt 

    If Reason2 is not null and Reason1 is Not null then New_Txt

   ( It have to generate 2 diff rows)

Base Table:

ID	R1	R2	Old_txt	New_txt
11200		PRO0003	A	B
11200	P03	Q08	G	D
11200	Q08	Q09	H	E

Required OutPut Like :

ID	R1	R2	Old_Txt	New_Txt
11200	Null	P03		B
11200	P03	Q08	G
11200	P03	Q08		D
11200	Q08	Q09	H
11200	Q08	Q09		E

[#2]

@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

[#3]

@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          

[#4]

@rodgerkong 

Thank you 🙂