?
Solved

Determine the difference between fields on different rows

Posted on 2009-07-11
3
Medium Priority
?
317 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:bryan_z
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831025
Do you have a primary key in order to "look at the rows adjacent to each other" ?
0
 
LVL 12

Accepted Solution

by:
expertsoul earned 1000 total points
ID: 24832427
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

Open in new window

0
 
LVL 8

Assisted Solution

by:Bobaran98
Bobaran98 earned 1000 total points
ID: 24832434
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

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question