Dynamic Unpivoting For Change Detection

Kevin Feasel

2017-07-07

T-SQL

Shane O’Neill has a script that dynamically unpivots a pair of rows and compares values column by column, storing the changes in XML:

Overall, the script is longer at nearly double the lines but where it shines is when adding new columns.
To include new columns, just add them to the table; to exclude them, just add in a filter clause.

So, potentially, if every column in this table is to be tracked and we add columns all the way up to 1,024 columns, this code will not increase.
Old way: at least 6,144.
New way: at least 2,048.
Dynamic: no change

Read on for that script.  Even though his developer ended up not using his solution, Shane has made it available for the rest of the world so that some day, someone else can have the maintenance nightmare of trying to root out a bug in the process.

Related Posts

Comparing Ranking Functions

Doug Kline compares three window functions:  RANK, DENSE_RANK, and ROW_NUMBER: — so let’s say that we’ve created a contest — places in the contest (top place, 2nd place, etc.) — will be determined by the test score — in other words, we’re not so concerned with the raw score — but rather, we’re interested in […]

Read More

Updating Data In Common Table Expressions

Kenneth Fisher shows that you can directly update a table referenced in a common table expression: CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31