SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Column compare
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

byka
Starting Member

18 Posts

Posted - 08/18/2014 :  12:55:35  Show Profile  Reply with Quote
How can I compare column in the same table and show only column and value that has been changed.

declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t
SELECT '11111','Text1','Text2','Text3'

insert into @t
SELECT '11111','Text1','Text2','Text4'



END RESULTS:
Column Name Old New
Column 3 |'Text3' |'Text4'

byka

gbritton
Flowing Fount of Yak Knowledge

1115 Posts

Posted - 08/18/2014 :  13:48:23  Show Profile  Reply with Quote
here's one way:


declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))
declare @t1 table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t 
SELECT '11111','Text1','Text2','Text3'

insert into @t1
SELECT '11111','Text1','Text2','Text4'


select 
	case 
		when t.text1 <> t1.text1 then 1 
		when t.text2 <> t1.text2 then 2
		when t.text2 <> t1.text3 then 3
	end as [Column],

	case 
		when t.text1 <> t1.text1 then t.text1
		when t.text2 <> t1.text2 then t.text2
		when t.text2 <> t1.text3 then t.text3
	end as Old,

	case 
		when t.text1 <> t1.text1 then t1.text1
		when t.text2 <> t1.text2 then t1.text2
		when t.text2 <> t1.text3 then t1.text3
	end as New

from @t t
join (
select * from @t
except select * from @t1
) t1
on t.GroupID = t1.GroupID
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 08/18/2014 :  14:12:00  Show Profile  Reply with Quote
do you have more generic examples because I need to do this for different tables with different scheema

byka
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1115 Posts

Posted - 08/18/2014 :  14:15:48  Show Profile  Reply with Quote
YOu could build a similar query using dynamic SQL, but perhaps the right answer is Change Tracking. Here's a series of articles on the topic:

http://solutioncenter.apexsql.com/methods-for-auditing-sql-server-data-changes-part-i-change-tracking/
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 08/18/2014 :  14:24:29  Show Profile  Reply with Quote
I have adjusted and it stopped working...could you tell me why ?

declare @t table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX), DeductibleNotes VARCHAR(MAX), NotSubjectToOOPLimit VARCHAR(MAX))
declare @t1 table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX),DeductibleNotes VARCHAR(MAX),NotSubjectToOOPLimit VARCHAR(MAX))

insert into @t (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )
SELECT '11111','NotSubjectToDeductible 4','DeductibleNotes','NotSubjectToOOPLimit '

insert into @t1 (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )
SELECT '11111','NotSubjectToDeductible ','DeductibleNotes','NotSubjectToOOPLimit 5'



select
case
when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then 'NotSubjectToDeductible'
when t.DeductibleNotes <> t1.DeductibleNotes then 'DeductibleNotes'
when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then 'NotSubjectToOOPLimit'
else 'na'
end as [Column],

case
when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then t.NotSubjectToDeductible
when t.DeductibleNotes <> t1.DeductibleNotes then t.DeductibleNotes
when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then t.NotSubjectToOOPLimit
end as Old,

case
when t.NotSubjectToDeductible <> t1.NotSubjectToDeductible then t1.NotSubjectToDeductible
when t.DeductibleNotes <> t1.DeductibleNotes then t1.DeductibleNotes
when t.NotSubjectToOOPLimit <> t1.NotSubjectToOOPLimit then t1.NotSubjectToOOPLimit
end as New

from @t t
join (
select * from @t
except select * from @t1
) t1
on t.GroupID = t1.GroupID




byka
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1115 Posts

Posted - 08/18/2014 :  15:04:12  Show Profile  Reply with Quote
Well let's think about it. In the subquery, we say, "Give me all rows of t that are not in t1. We get one row back, which we join with the one row in t. In other words, the old and the new rows are the same. You can reverse the subsquery:


select * from @t1
except select * from @t


which says give me all the rows of t1 that are not also in t. We get one row back but this time its the row from t1. Makes more sense I think.

Note, however, that this is not set up to find all columns of t1 that are different...just the first one. To do all columns we're going to have to do more work. That is, if that's what you want.

The basic idea is to use set operations (EXCEPT, INTERSECT, UNION) to find rows that differ, then examine the rows column by column to find out what changed.
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 08/18/2014 :  15:16:23  Show Profile  Reply with Quote
With Union it will list full row but I need to specify in what column and what value changed.

byka
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1115 Posts

Posted - 08/18/2014 :  15:24:51  Show Profile  Reply with Quote
quote:
Originally posted by byka

With Union it will list full row but I need to specify in what column and what value changed.

byka



Yes, of course. I wasn't suggesting you use Union, just listing the set operations. For your problem, EXCEPT is the right choice
Go to Top of Page

byka
Starting Member

18 Posts

Posted - 08/18/2014 :  15:38:43  Show Profile  Reply with Quote
Here is what I come up with based on your advise... however not getting what I need...
sorry I am new to sql so need some help ....

My end result should look this this:
Column Name OldValue newValue
NotSubjectToDeductible NotSubjectToDeductible otSubjectToDeductible
4

NotSubjectToOOPLimit NotSubjectToOOPLimit NotSubjectToOOPLimit 5
declare @t table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX), DeductibleNotes VARCHAR(MAX), NotSubjectToOOPLimit VARCHAR(MAX))
declare @t1 table (GroupID CHAR(6),NotSubjectToDeductible VARCHAR(MAX),DeductibleNotes VARCHAR(MAX),NotSubjectToOOPLimit VARCHAR(MAX))

insert into @t (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )
SELECT '11111','NotSubjectToDeductible 4','DeductibleNotes','NotSubjectToOOPLimit '

insert into @t1 (GroupID ,NotSubjectToDeductible , DeductibleNotes , NotSubjectToOOPLimit )
SELECT '11111','NotSubjectToDeductible ','DeductibleNotes','NotSubjectToOOPLimit 5'

SELECT *
FROM @t
EXCEPT
SELECT *
FROM @t1

byka
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1115 Posts

Posted - 08/19/2014 :  08:31:43  Show Profile  Reply with Quote
OK -- I replaced the last part (the selects) with thi:


SELECT * from (
(select * FROM @t1 EXCEPT SELECT * FROM @t)
UNION
(select * FROM @t EXCEPT SELECT * FROM @t1)) s


That is saying, "give me all the rows in @t1 that are not found in @t and also give me all the rows in @t that are not found in @t1."

It's important that you not only get a working solution, but that you understand why it works (or doesn't!). In this case I'm just using SQL Set operations which come directly from Relational Algebra upon which SQL is based.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000