Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Column compare

Author  Topic 

byka
Starting Member

18 Posts

Posted - 2014-08-18 : 12:55:35
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 13:48:23
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 - 2014-08-18 : 14:12:00
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 14:15:48
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 - 2014-08-18 : 14:24:29
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 15:04:12
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 - 2014-08-18 : 15:16:23
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 15:24:51
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 - 2014-08-18 : 15:38:43
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 08:31:43
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
   

- Advertisement -