Forum Discussion
Sqlserver- Not Null Cases
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 |
- LainRobertsonSilver Contributor
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
- rodgerkongIron Contributor
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
- saikumar86kCopper Contributor