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.
| Author |
Topic |
|
syclick
Starting Member
3 Posts |
 Posted - 2010-09-28 : 16:54:12
|
| If I've got two rows in the same table, and I'd like to return a list of columns (along with the two non-matching values) whose values do not match in the two rows, how would I accomplish that in a generic way that I wouldn't have to specify every column in every table? (Tables will contain columns of varying datatypes and nullability)Say, for example, I've got the following table:CREATE TABLE #TempTable(PK_ID bigint, Col1 int, Col2 int, Col3 int, Col4 int, Col5 int)INSERT INTO #TempTableSELECT 1, 2, 4, 6, 8, 10 UNION ALLSELECT 2, 2, 3, 6, 9, 10And I want to derive the following:COLNAME | Val1 | Val2---------------------PK_ID | 1 | 2Col2 | 4 | 3Col4 | 8 | 9Any pointers?EDIT: Scenario will ALWAYS be performed on two rows, no more, no less |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-28 : 18:03:51
|
| There are a few complications:1. Doing this in a "generic way" for different tables will involve fairly complicated dynamic SQL2. You cannot unpivot the columns as Val1 and Val2 if they are different data types3. Unpivoting PK_ID will probably break your results, better to have it as its own column.Can you explain what you're trying to accomplish? It looks a bit like an audit table but it's pretty unusual. |
 |
|
|
syclick
Starting Member
3 Posts |
Posted - 2010-09-28 : 18:24:12
|
| I am trying to create an audit report of what columns have changed between the two rows I am comparing.Specifically, I'd like to be able to say:First Name changed from Joe to BobLast Name changed from Smith to CarsonMarital Status changed from Married to DivorcedOf course, that's not the specific output I'm looking to get out of this query; I'm trying to get it in the format of COLUMN_NAME, ROW1VALUE, ROW2VALUE for every column changed. |
|
|
|
kevlangdo
Starting Member
5 Posts |
Posted - 2010-09-28 : 21:49:08
|
| Are you trying to determine what values (columns) have change for a particular row?If so, you could put a trigger on the table for each column and when a change happens to a specific column a new entry would be entered into audit table.Say that you main table goes something like this Empid#, FirstName, LastNameThen your Audit table would have Columns Audit#, Empid#, FirstName, LastNameThen your could have a stored procedure that checks for multiple rows with matching Empid# and perform an Exception likeSelect Empid#, FirstName, LastName from #FirstMatchingRecordExceptSelect Empid#, FirstName, LastName from #SecondMatchingRecordHope this points you in the right directionKevin LanguedocSr BI Developerwww.kcodebook.com/wp |
|
|
|
syclick
Starting Member
3 Posts |
Posted - 2010-09-29 : 12:29:33
|
| Kevin,The audit table already exists, but it isn't on a per-column basis. Every table we want to keep a history on has a corresponding history table, which mirrors the original plus a few extra columns (pk, date/time). On every insert/update, an entire copy of the row affected is dumped into the history table.I've then got a sproc that take an ID and table name, and returns the two most recently archived rows that match. It's these two rows that I'm trying to compare.Granted, I could do this with a cursor and some dynamic SQL, but I have a feeling the performance would be horrible. And considering how often this needs to be run (a lot), it would really bog the system down. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Difference between 2 rows of same table