# Determine the difference between fields on different rows

Is there a way to determine the difference between two rows using SQL or a stored procedure. For example, I have a table with the following results:

ROW 1: John, Dawn
ROW 2: John, Doe
ROW 3: Sam, Doe
ROW 4: Sam, Doe
ROW 5: John, Doe

I want to retrieve the complete list and then look at the rows adjacent to each other for what values in the fields are different.  In principle ROW 1 would be compared to ROW 2 and ROW 2 to ROW 3, etc. Thus (in theory...)

RESULT 1: Dawn
RESULT 2: John
RESULT 3:
RESULT 4: Sam
RESULT 5: John,Joe

Thoughts?

Bryan

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Do you have a primary key in order to "look at the rows adjacent to each other" ?
0
Commented:
This query should work.

``````CREATE TABLE #abc(
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT INTO #abc VALUES('John', 'Dawn')
INSERT INTO #abc VALUES('John', 'Doe')
INSERT INTO #abc VALUES('Sam', 'Doe')
INSERT INTO #abc VALUES('Sam', 'Doe')
INSERT INTO #abc VALUES('John', 'Doe')

SELECT A.FirstName, A.LastName, B.FirstName, B.LastName,
CASE WHEN A.FirstName != B.FirstName AND A.LastName != B.LastName THEN A.FirstName + ',' + B.LastName
WHEN A.FirstName != B.FirstName THEN A.FirstName
WHEN A.LastName != B.LastName  THEN A.LastName
ELSE NULL END as [Result Column]
from
(SELECT Row_Number() OVER (ORDER BY LastName) as RowNo,* from #abc) A,
(SELECT Row_Number() OVER (ORDER BY LastName) as RowNo,* from #abc) B
WHERE A.RowNo = B.RowNo - 1
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
As acperkins has indicated, it would be helpful if you had a unique key associated with each result-- a key that would indicate the order.  I've written a stored procedure below which assumes your table looks like this:

myTable
=======
ID    field1    field2
================
1    John    Dawn
2    John    Doe
3    Sam    Doe
4    Sam    Doe
5    John    Doe

This will give you exactly the results you asked for.  This stored procedure is executed in three stages using nested queries; allow me to walk you through what each level of the query does:
• LEVEL 1 - grabs the three fields from your table AND determines the ID of the next entry in sequence
• LEVEL 2A - returns all but the last entry in sequence - takes the dataset from LEVEL 1 (called "This") and pulls the three fields for the next entry in sequence (called "Next"); compares the fields and, if there are differences, writes them to the fields "field1diff" and "field2diff"
• LEVEL 2B - returns the last entry in sequence - this is processed separately because it has no "next" entry to compare against
• LEVEL 3 - takes the dataset from LEVEL 2 and combines any differences into a single field called "Differences", comma delimiting if there's more than one difference
I haven't tested any of this, but hopefully it meets your needs.  If not, let me know!  You may, of course, choose to do away with LEVEL 3 if you want to be able to know which fields the differences lie in (rather than grabbing a single comma-delimited list).

``````CREATE PROCEDURE [dbo].[myStoredProc]

AS

SET NOCOUNT ON

--LEVEL 3 of query
SELECT

TTT.ID,
TTT.field1diff
+ CASE
WHEN field1diff<>'' AND field2diff<>'' THEN ','
ELSE ''
END
+ TTT.field2diff as Differences

FROM	(

--LEVEL 2A of query
SELECT

This.ID,
CASE
WHEN Next.field1=This.field1 THEN ''
ELSE This.field1
END as field1diff,
CASE
WHEN Next.field2=This.field2 THEN ''
ELSE This.field2
END as field2diff

FROM
myTable Next,
(

--LEVEL 1 of query
SELECT
TT.ID,
ISNULL((SELECT Top 1 ID FROM myTable
WHERE ID>TT.ID ORDER BY ID ASC),-1) as nextID,
TT.field1,
TT.field2

FROM
(SELECT * FROM myTable
ORDER BY ID ASC
) TT

) This

WHERE
Next.ID=This.nextID

--LEVEL 2B of query
UNION SELECT Top 1
ID,
field1 as field1diff,
field2 as field2diff

FROM myTable
ORDER BY ID DESC

) TTT

ORDER BY

TTT.ID ASC
``````
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.