# How to compare columns across a row while ignoring null values

• ### 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

• ```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 Wednesday, September 11, 2019 9:16 PM
Wednesday, September 11, 2019 8:44 PM
• 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 Wednesday, September 11, 2019 9:09 PM
• Marked as answer by 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 Wednesday, September 11, 2019 9:16 PM
Wednesday, September 11, 2019 8:44 PM
• 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 Wednesday, September 11, 2019 9:09 PM
• Marked as answer by 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