compare more than 2 columns in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

compare more than 2 columns in table

Author

Topic

notDBguru
Starting Member

6 Posts

Posted - 2010-08-17 : 16:20:09

Hello,
I need help to compare values in 4 columns in the same table.
my table like this below:

Code col1 col2 col3 col4
123 E D D NULL
222 NULL E E NULL
333 NULL X3 NULL NULL
......
I would like to compare col1, col2, col3, col4 and get only records that not the same between any columns.
Note: ignore NULL or Blank cell.
For the table above I only want to get code 123 and ignore the rest.

Thank you!

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 16:23:44

Here's a hint to posting...

use [ code] [ /code] tags to dis[play data (no space in the tag)


Code col1 col2 col3 col4
123  E    D    D    NULL
222  NULL E    E    NULL
333  NULL X3   NULL NULL

but huh?

Don't you mean 333???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-18 : 11:34:22

See if this works for all set of data


declare @t table(Code int,col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
insert into @t
select 123,  'E',    'D',    'D',    NULL union all
select 222,  NULL, 'E',    'E',    NULL union all
select 333,  NULL, 'X3',   NULL, NULL

select code from
(
select code, col1 from @t
union all
select code, col2 from @t
union all
select code, col3 from @t
union all
select code, col4 from @t
) as t
group by code
having count(distinct col1)>1

Madhivanan

Failing to plan is Planning to fail

notDBguru
Starting Member

6 Posts

Posted - 2010-08-18 : 13:44:04

hello,

your code works and it's neat. Only one issue, it includes null and blank in comparison.
To be more specific, below is example:
col1 has value 'J'
col2 has value 'J'
col3 has value 'J'
col4 has value NULL then I don't want this record in result list.
here is another example:
col1 has value 'E'
col2 has value BLANK
col3 has value 'J'
col4 has value NULL then the comparison will be the 'E' and 'J', this should be in result list.

Thank you for your help! have a great day.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-18 : 14:06:37

quote:
Originally posted by notDBguru

hello,

your code works and it's neat. Only one issue, it includes null and blank in comparison.
To be more specific, below is example:
col1 has value 'J'
col2 has value 'J'
col3 has value 'J'
col4 has value NULL then I don't want this record in result list.
here is another example:
col1 has value 'E'
col2 has value BLANK
col3 has value 'J'
col4 has value NULL then the comparison will be the 'E' and 'J', this should be in result list.

Thank you for your help! have a great day.

Thats what Madhi's code seems to do...Added your sample data to the list.

declare @t table(Code int,col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
insert into @t
select 123,  'E',    'D',    'D',    NULL union all
select 222,  NULL, 'E',    'E',    NULL union all
select 333,  NULL, 'X3',   NULL, NULL  union all
select 444,  'J', 'J',   'J', NULL  union all
select 555,  'E', ' ',   'J', NULL

select code from
(
select code, col1 from @t
union all
select code, col2 from @t
union all
select code, col3 from @t
union all
select code, col4 from @t
) as t
group by code
having count(distinct col1)>1

notDBguru
Starting Member

6 Posts

Posted - 2010-08-18 : 16:03:06

here is data

declare @t table(Code int,col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
insert into @t
select 123, 'B', 'B', 'B', NULL union all
select 222, '1', '1', '1', NULL union all
select 333, 'E8', 'E8', 'E8', 'X1' union all
select 444, 'J', 'J', 'J', '' union all
select 555, 'E', ' ', 'J', NULL

code 444 should be ignored in result.

thanks!

notDBguru
Starting Member

6 Posts

Posted - 2010-08-18 : 18:17:34

Just want to add a quick note, the result list is 333,444,555, but 444 shouldn't be in there.

notDBguru
Starting Member

6 Posts

Posted - 2010-08-18 : 19:21:01

I found way to work around, update empty with null before the comparison and it works. I'm open with solution if you have a better way to handle this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources