September 22, 2010 at 4:45 am
Hi, first post, I have search the forum but found nothing directly related to my issue.
Given 2 rows of a table I need to find the column names of any column values that are different between the two rows.
The usage of this is for a reporting purpose to check for version changes. All rows have a unique version column. So I will take a version 2 and version-1 (1) and compare the two rows. There can be many data columns in the rows, but they are both from the same table.
Then I need an output which will show me only the column names that have changed values between the rows. Added bonus would include the actual values from each row compared.
Can anyone help with this?
Thanks in advance,
Aus.
September 22, 2010 at 7:14 am
The easiest way to do this is to use something like SQL DataCompare.
The only way to do this without resorting to some type of dynamic SQL is something like this:
Select
Case
When T1.col1 <> T2.col1 Then 'Col 1 Changed.'
Else 'No change.'
End + 'Values(' + T1.col1 + ', ' + T2.col1 + ')' as col1
...
FROM
table1 T1 JOIN
table2 T2
ON T1.key = T2.key AND
T1.version = T2.version - 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2010 at 11:54 am
It's actually possible to do this without dynamic SQL and still have it be general enough that it will automatically handle changes to the table definitions.
This hasn't been completely optimized, but it will give you a general idea. It uses the FOR XML clause to get it into a particular format and then shreds it in a way that you've partially transposed the columns and rows from the original so that you can do a direct comparison of the values. All of the values are cast as varchar(max) because that will should handle most of the types.
DECLARE @x xml
SET @x = (
SELECT
t2.[Key] AS [@key]
, t2.Version AS [@version]
, ( SELECT t1.* FOR XML PATH('t1'), TYPE ) AS [*]
, ( SELECT t2.* FOR XML PATH('t2'), TYPE ) AS [*]
FROM Table1 AS t1
INNER JOIN Table2 AS t2
ON t1.[Key] = t2.[Key]
AND t1.WF_Def_Step_ID = t2.WF_Def_Step_ID - 1
FOR XML PATH('row'), ROOT('root')
);
WITH Nodes AS (
SELECT
C.value('../../@key', 'int') AS [Key]
, C.value('../../@version', 'int') AS Version_ID
, C.value('local-name(..)', 'varchar(255)')AS Version_Alias
, C.value('local-name(.)', 'varchar(255)') AS Field
, C.value('.', 'varchar(max)') AS Val
FROM @x.nodes('/root/row/*/*') AS T(C)
)
SELECT [Key], Version_ID, Field
, Max(CASE Version_Alias WHEN 't1' THEN Val END) AS Table1
, Max(CASE Version_Alias WHEN 't2' THEN Val END) AS Table2
FROM Nodes
GROUP BY [Key], Version_ID, Field
HAVING Max(CASE Version_Alias WHEN 't1' THEN Val END) <> Max(CASE Version_Alias WHEN 't2' THEN Val END)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 12, 2011 at 8:58 am
Hi,
I've got similar problem: but I need to compare more rows, not only two.
I used your great script. That teach me lot.
Problem:compare field values of several releases; result show only different field,field value
>>every row has productId and ReleaseID and many other fields.
So I need see differences between rows of same productID and different releaseIDs(previous) for given productID and releaseID .
f.e: productId=10 ;releaseID=5 need to see different field values of releases 1-4
I have changed your code for my purpouse.
DECLARE @x xml
SET @x = (
SELECT
t1.prodid AS [@ProdID]
,t1.releaseid AS [@relID]
,t2.RELEASEID as [@relID2]
, ( SELECT t1.* FOR XML PATH('t1'), TYPE ) AS [*]
, ( SELECT t2.* FOR XML PATH('t2'), TYPE ) AS [*]
FROM Table AS t1
JOIN Table AS t2
ON t1.prodid = t2.prodid
and t1.PRODID=@SelectedProduct
and t1.RELEASEID=@selectedRelease
and t2.RELEASEID<t1.RELEASEID
FOR XML PATH('row'), ROOT('root')
);
--select @x ;
WITH Nodes AS (
select
c.value('../../@ProdID', 'varchar(255)')as prodID
, C.value('../../@relID', 'varchar(255)') AS releaseID
, C.value('../../@relID2', 'varchar(255)') AS releaseID2
, C.value('local-name(..)', 'varchar(255)')AS Version_Alias
, C.value('local-name(.)', 'varchar(255)') AS Field
, C.value('.', 'varchar(max)') AS Val
From @x.nodes('/root/row/*/*') as T(c)
)
select old.releaseID2,old.Field,old.Val ValuePrev ,new.Val ValueNow
from
(select * from Nodes where Version_Alias='t1')new
join
(select * from Nodes where Version_Alias='t2')old
on new.releaseID2=old.releaseID2 and new.Field=old.Field
where new.Val<>old.Val
Thanks for advices.
Hannibal.
February 13, 2016 at 7:43 am
This strategy works as expected. It does return columns that are different on all matching records based on the Key. However, it does not return columns when either record is null.
Is this your experience and/or can you think of a way to deal with that reality.
Thanks,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply