Compare two rows and identify columns whose values are different

Question

The Situation

We have an application where we store machine settings in a SQL table. When the user changes a parameter of the machine, we create a "revision", that means we insert a row into a table. This table has about 200 columns. In our application, the user can take a look on each revision.

The Problem

We want to highlight the parameters that have changed since the last revision.

The Question

Is there an SQL-only way to get the column names of the differences between two rows?

An Example

ID | p_x | p_y | p_z
--------------------
11 | xxx | yyy | zzz

12 | xxy | yyy | zzy

The query should return p_x and p_z.

EDIT

The table has 200 columns, not rows...

MY WAY OUT

My intention was to find a "one-line-SQL-statement" for this problem.

I see in the answers below, it's kind a bigger thing in SQL. As there is no short, SQL-included solution for this problem, solving it in the backend of our software (c#) is of course much easier!

But as this is not a real "answer" to my question, I don't mark it as answered.

Thanks for the help.

Answer 1

You can use unpivot and pivot. The key is to transpose data so that you can use where [11] != [12].

WITH CTE AS (
    SELECT * 
    FROM 
    (
        SELECT ID, colName, val
        FROM tblName
        UNPIVOT
        (
            val
            FOR colName IN ([p_x],[p_y],[p_z])
        ) unpiv
    ) src
    PIVOT
    (
        MAX(val)
        FOR ID IN ([11], [12])
    ) piv
)
SELECT colName
--SELECT *
FROM CTE WHERE [11] != [12]

If there are only a few columns in the table, it's easy to simply put [p_x],[p_y],[p_z], but obviously it's not convenient to type 50 or more columns. Even though you may use this trick to drag and drop, or copy/paste, the column names from the table, it's still bulky. And for that, you may use the SELECT * EXCEPT strategy with dynamic sql.

DECLARE @TSQL NVARCHAR(MAX), @colNames NVARCHAR(MAX)
SELECT @colNames = COALESCE(@colNames + ',' ,'') + [name] 
FROM syscolumns WHERE name  <> 'ID' and id = (SELECT id FROM sysobjects WHERE name = 'tablelName')

SET @TSQL = '
    WITH CTE AS (
        SELECT * 
        FROM 
        (
            SELECT ID, colName, val
            FROM tablelName
            UNPIVOT
            (
                val
                FOR colName IN (' + @colNames + ')
            ) unpiv
        ) src
        PIVOT
        (
            MAX(val)
            FOR ID IN ([11], [12])
        ) piv
    )
    --SELECT colName
    SELECT *
    FROM CTE WHERE [11] != [12]
'
EXEC sp_executesql @TSQL

Answer 2

You say:

 We want to highlight the parameters that have changed since the last revision.

This implies that you want the display (or report) to make the parameters that changed stand out.

If you're going to show all the parameters anyway, it would be a lot easier to do this programmatically in the front end. It would be a much simpler problem in a programming language. Unfortunately, not knowing what your front end is, I can't give you particular recommendations.

If you really can't do it in the front end but have to receive this information in a query from the database (you did say "SQL-only"), you need to specify the format you'd like the data in. A single-column list of the columns that changed between the two records? A list of columns with a flag indicating which columns did or didn't change?

But here's one way that would work, though in the process it converts all your fields to nvarchars before it does its comparison:

1. Use the technique described here (disclaimer: that's my blog) to transform your records into ID-name-value pairs.

2. Join the resulting data set to itself on ID, so that you can compare the values and print those that have changed:

    with A as (    
   --  We're going to return the product ID, plus an XML version of the     
   --  entire record. 
   select  ID    
    ,   (
         Select  *          
         from    myTable          
         where   ID = pp.ID                            
         for xml auto, type) as X 
   from    myTable pp )
   , B as (    
   --  We're going to run an Xml query against the XML field, and transform it    
   --  into a series of name-value pairs.  But X2 will still be a single XML    
   --  field, associated with this ID.    
   select  Id        
      ,   X.query(         
          'for $f in myTable/@*          
          return         
          <data  name="{ local-name($f) }" value="{ data($f) }" />      
          ') 
          as X2 from A 
   )
   ,    C as (    
    --  We're going to run the Nodes function against the X2 field,  splitting     
    --  our list of "data" elements into individual nodes.  We will then use    
    -- the Value function to extract the name and value.   
    select B.ID as ID  
      ,   norm.data.value('@name', 'nvarchar(max)') as Name  
      ,   norm.data.value('@value', 'nvarchar(max)') as Value
   from B cross apply B.X2.nodes('/myTable') as norm(data))
   
   -- Select our results.
   
   select *
   from ( select * from C where ID = 123) C1
   full outer join ( select * from C where ID = 345) C2
       on C1.Name = c2.Name
   where c1.Value <> c2.Value 
     or  not (c1.Value is null and c2.Value is null)