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.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-04-06 : 11:26:14
|
| Hey,If possible, using an ad-hoc query, I have a record I wish to compare to other records in the same table. So, let's say this table has 4 columns: Col1, Col2, Col3, and Col4. And with this record, I'm attempting to find it's mirror record; however, it's not a true mirror record, since Col4 is different. The end goal of this problem is to find out what the value of Col4 is in this almost-mirror record. So, what is the best way to do this? Must I compare every single column or is there a way of saying "all columns are equal except Col4"?In reality, I have about 20 columns in this one table and it's rather tedious to check if 19 columns are equal; if there's a shorter way, I'd love to hear it.Thank you. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-06 : 11:45:44
|
| Not sure if I am understanding this right. Sampe data and expected output will make it clear. |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-04-06 : 11:55:56
|
| I don't think there's a way to do it in 2000.I simply want to compare two records in one table, with the conditions that all but one of their columsn are equal. I simply don't want to have to type out "... on a.col1=b.col1 and a.col2=b.col2 and ... and a.col19=b.col19 and a.col20 <> b.col20. |
|
|
|
dsindo
Starting Member
45 Posts |
Posted - 2009-04-15 : 15:15:46
|
| try something like this..declare @column_name1 varchar(200),@cols int, @col int ,@column_name2 varchar(200)select @cols=max(ordinal_position),@col=min(ordinal_position) from information_schema.columns where table_name = 'TABLE1' print @col print @cols while @cols>=@col begin select @column_name1= column_name from information_schema.columns where table_name = 'TABLE1' and ordinal_position=@col select @column_name2= column_name from information_schema.columns where table_name = 'TABLE2' and ordinal_position=@col ...do your thing here ex. exec ('select '+@column_name1 +' from TABLE1' ) set @col=@col+1 end |
|
|
|
|
|
|
|
|
comparing two records' cells