none
How to compare columns across a row while ignoring null values RRS feed

  • Question

  • Hi All,

    Ok.  I'm stumped.  I can't seem to solve this seemingly simple problem.  How do I compare columns across a row while ignoring columns that have null values?

    declare @foo table
    (
    	id		integer,
    	a		varchar(10),
    	b		varchar(10),
    	c		varchar(10),
    	d		varchar(10),
    	e		varchar(10),
    	f		varchar(10)
    );
    
    insert into @foo values
    	(1,'aa','aa','aa',null,null,null),
    	(2,'a2',null,'a2',null,'a2',null),
    	(3,'a3','a3','a3',null,null,'XX'),
    	(4,'b4','b4','b44','b4','b4','b4'),
    	(5,null,null,null,null,null,null);
    
    select *,
    	(
    		case
    			when id = 1 then 1
    			when id = 2 then 1
    			when id = 3 then 0
    			when id = 4 then 0
    			when id = 5 then 1
    		end
    	) as DesiredMatchValue
    from 
    	@foo
    order by
    	id;

    Rules:

    1. I want to calculate the column "DesiredMatchValue"  = 1 when all columns (a thru f) equal the same value.  The value can be any non-null value.  In my example, I hardcoded "DesiredMatchValue" so you can see the expected output!
    2. In the example above, row id's 3 and 4 do not "match", therefore they are equal to zero (0).  All other rows "match" so they equal one (1).
    3. In row id 5, all values are null, so they "match", i.e., equal to one (1).

    In my real table I have more than 6 columns, but the rule is the same ... all non-null values need to match.

    Any ideas on how I can simply do this?

    Thanks!

    --Dan

    Wednesday, September 11, 2019 7:59 PM

Answers

  • declare @foo table
    (
    	id		integer,
    	a		varchar(10),
    	b		varchar(10),
    	c		varchar(10),
    	d		varchar(10),
    	e		varchar(10),
    	f		varchar(10)
    );
    
    insert into @foo values
    	(1,'aa','aa','aa',null,null,null),
    	(2,'a2',null,'a2',null,'a2',null),
    	(3,'a3','a3','a3',null,null,'XX'),
    	(4,'b4','b4','b44','b4','b4','b4'),
    	(5,null,null,null,null,null,null);
    
    select id,
    case when count(distinct col)<=1 then 1 else 0 end  DesiredMatchValue
    from 
    	@foo
    	cross apply(values (a),(b),(c),(d),(e),(f)  )   d(col)
    
    	group by id
    

    • Marked as answer by DJAdan Wednesday, September 11, 2019 9:16 PM
    Wednesday, September 11, 2019 8:44 PM
    Moderator
  • Hi Dan,

    A solution based on XQuery:

    declare @foo table
    (
    	id		INTEGER IDENTITY(1,1) PRIMARY KEY,
    	a		varchar(10),
    	b		varchar(10),
    	c		varchar(10),
    	d		varchar(10),
    	e		varchar(10),
    	f		varchar(10)
    );
    
    insert into @foo values
    	('aa','aa','aa',null,null,null),
    	('a2',null,'a2',null,'a2',null),
    	('a3','a3','a3',null,null,'XX'),
    	('b4','b4','b44','b4','b4','b4'),
    	(null,null,null,null,null,null);
    
    ;WITH rs AS 
    (
    	SELECT * 
    		, CAST('<root><r>' + COALESCE(a,'') + '</r><r>' + COALESCE(b,'') + '</r><r>' + COALESCE(c,'') + '</r><r>' + COALESCE(d,'') + '</r><r>'+ COALESCE(e,'') + '</r><r>' + COALESCE(f,'') + '</r></root>' AS XML) AS xmldata
    	FROM @foo
    )
    SELECT * 
        , xmldata.query('count(distinct-values(data(/root/r/text())))') AS DesiredMatchValue
    FROM rs;



    • Edited by Yitzhak Khabinsky Wednesday, September 11, 2019 9:09 PM
    • Marked as answer by DJAdan Wednesday, September 11, 2019 9:16 PM
    Wednesday, September 11, 2019 9:07 PM

All replies

  • Hi Dan,

    What is your SQL Server version?

    Wednesday, September 11, 2019 8:18 PM
  • Hi Yitzhak,

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Thanks!

    --Dan

    Wednesday, September 11, 2019 8:28 PM
  • declare @foo table
    (
    	id		integer,
    	a		varchar(10),
    	b		varchar(10),
    	c		varchar(10),
    	d		varchar(10),
    	e		varchar(10),
    	f		varchar(10)
    );
    
    insert into @foo values
    	(1,'aa','aa','aa',null,null,null),
    	(2,'a2',null,'a2',null,'a2',null),
    	(3,'a3','a3','a3',null,null,'XX'),
    	(4,'b4','b4','b44','b4','b4','b4'),
    	(5,null,null,null,null,null,null);
    
    select id,
    case when count(distinct col)<=1 then 1 else 0 end  DesiredMatchValue
    from 
    	@foo
    	cross apply(values (a),(b),(c),(d),(e),(f)  )   d(col)
    
    	group by id
    

    • Marked as answer by DJAdan Wednesday, September 11, 2019 9:16 PM
    Wednesday, September 11, 2019 8:44 PM
    Moderator
  • Hi Dan,

    A solution based on XQuery:

    declare @foo table
    (
    	id		INTEGER IDENTITY(1,1) PRIMARY KEY,
    	a		varchar(10),
    	b		varchar(10),
    	c		varchar(10),
    	d		varchar(10),
    	e		varchar(10),
    	f		varchar(10)
    );
    
    insert into @foo values
    	('aa','aa','aa',null,null,null),
    	('a2',null,'a2',null,'a2',null),
    	('a3','a3','a3',null,null,'XX'),
    	('b4','b4','b44','b4','b4','b4'),
    	(null,null,null,null,null,null);
    
    ;WITH rs AS 
    (
    	SELECT * 
    		, CAST('<root><r>' + COALESCE(a,'') + '</r><r>' + COALESCE(b,'') + '</r><r>' + COALESCE(c,'') + '</r><r>' + COALESCE(d,'') + '</r><r>'+ COALESCE(e,'') + '</r><r>' + COALESCE(f,'') + '</r></root>' AS XML) AS xmldata
    	FROM @foo
    )
    SELECT * 
        , xmldata.query('count(distinct-values(data(/root/r/text())))') AS DesiredMatchValue
    FROM rs;



    • Edited by Yitzhak Khabinsky Wednesday, September 11, 2019 9:09 PM
    • Marked as answer by DJAdan Wednesday, September 11, 2019 9:16 PM
    Wednesday, September 11, 2019 9:07 PM
  • Hi Jingyang & Yitzhak,

    These are most excellent answers.  Thank you.

    Jingyang:  I always forget about CROSS APPLY.  Very clever.

    Yitzhak:  Your answer returns 1 if match, 2 if mismatch, and 0 if all NULL.  This would still work for me, although, "0" as ALL NULL is the same in my business rule as "1".

    Thanks again!

    --Dan

    Wednesday, September 11, 2019 9:19 PM
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    While you question has been answered, I still like to point out that you are using a very ancient version. You should absolutely install SP3 for SQL 2008 R2. There is absolutely no reason for running the RTM version!

    Of course, it is even better to upgrade to a later edition of SQL Server, but you should not be on RTM!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, September 11, 2019 9:59 PM
  • Hi Erland,

    You are correct.  We are in the process of upgrading to SQL Server 2017 ...

    Thank you.

    --Dan

    Thursday, September 12, 2019 12:13 PM