Forum Discussion

saikumar86k's avatar
saikumar86k
Copper Contributor
Sep 17, 2024

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:

IDR1R2Old_txtNew_txt
11200 PRO0003AB
11200P03Q08GD
11200Q08Q09HE

 

Required OutPut Like :

 

IDR1R2Old_TxtNew_Txt
11200NullP03 B
11200P03Q08G 
11200P03Q08 D
11200Q08Q09H 
11200Q08Q09 E
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor

    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

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    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          

     

Resources