Compare two records of the same table

Question

Post reply

Compare two records of the same table

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
September 16, 2011 at 7:28 am

#149689

I need to traverse through each record in table and compare between the two records of the same table? Is there a better way of doing this instead of using a cursor?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Yes, definitely. Use a self join.

Can you post table script and tell us a bit more about your issue?

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Self join, I mean something like this:

SELECT A.*

FROM SomeTable AS A

INNER JOIN SomeTable AS B

ON A.SomeColumn = B.SomeOtherColumn

-- Gianluca Sartori

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The use of windowed functions comes to mind as well (row_number).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 4

join the table to itself is what I would recommend... without code we can't be more specific... rarely is a cursor the best option.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

SQL_Surfer (9/16/2011)
I need to traverse through each record in table and compare between the two records of the same table? Is there a better way of doing this instead of using a cursor?

by "record"...do you mean a column or a complete row of data, eg all columns?

maybe TableDiff.exe will assist

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 6

Given the table as below

Col1Col2Col3

ABABC

ABCDE

ABEFG

I need to be able to display only one row as below

A B ABC;CDE;EFG

Can somebody help?

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

It looks like you are trying to concatenate the 3rd column based on the first 2 columns. If so, here is an excellent article on concatenating columns (turning rows into columns):

http://www.sqlservercentral.com/articles/Test+Data/61572/

Todd Fifield

mhike2hale SSC Eights! Points: 858 More actions · Answer 8

can you try this?

declare @a varchar(255) = ''

select @a = @a + Col1 + Col2 + Col3 + ';'

from

(

select 'A'[Col1], 'B'[Col2], 'ABC'[Col3]

union all

select 'A', 'B', 'CDE'

union all

select 'A', 'B', 'EFG'

) b

select @a

"Often speak with code not with word,
A simple solution for a simple question"

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 9

Thanks tfifield. The use of FOR XMl mentioned in the article did the trick. Is there any other way by just using the self join?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

SQL_Surfer (12/7/2011)
Thanks tfifield. The use of FOR XMl mentioned in the article did the trick. Is there any other way by just using the self join?

Yes, but not with a variable # of rows. If you knew, for example, that you had an EAV table that always had firstName, LastName, and Address entries, you could self-join your table with those in the ON clauses. With variable sets like this, the FOR XML method is your best bet.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA