Solved

Determine the difference between fields on different rows

Posted on 2009-07-11
3
309 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
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 250 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 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now