none
SQL to compare rows in same table where column values have changed (without defining column names) RRS feed

  • Question

  • Hi, I'm looking for SQL to compare rows within a single audit trail table so  I can produce a report which shows me only the columns which have changed from one version of a record to the next for each version comparison. (I.e. version 1 to 2, version 2 to 3).The "version" is an identity column which increments by 1 every time a record is changed within my application. However, I don't want to specify the column names in the query itself, as the table has 200+ columns and more are always being added.

    Sample of table:

    versionid accountid balance date
    1 1 1450.22 01/02/2013
    2 1 1450.22 04/02/2013
    3 1 1954.34 04/02/2013
    1 2 3420.00 06/02/2013
    2 2 3420.00 06/02/2013
    3 2 4975.11 06/02/2013

    The result I want to see is:

    versionid accountid balance date
    1 1 1450.22 01/02/2013
    2 1   04/02/2013
    3 1 1954.34  
    1 2 3420.00 06/02/2013
    3 2 4975.11  

    So the first row (when record is created) and then only rows where column values have changed (other than the primary key). Versionid and accountid are the primary key columns.

    The reason version 2 for accountid 2 is missing in the above result set is because no column values have changed from version 1 to 2.
    Is there an easy way to produce the results I want for all columns in the table without explicitly naming them in the query? Ideally avoiding using cursors.

    Thanks in advance.


    • Edited by Reginald Huxley Thursday, July 18, 2013 10:22 AM formatting error
    Thursday, July 18, 2013 10:13 AM

All replies

  • No - there is no easy way, especially in tsql.  However, there are reporting environments where this can be done relatively easily.  Unfortunately, most tools do NOT automatically reconfigure their output to respond to schema changes due to the complexity involved with the logic.  It could be coded in an application, but nothing out-of-the-box. 

    One last comment - any logic that might be used depends on the actual key columns that allow one row to be associated with its siblings.  That would be the primary key columns of the table that is the source of this data.  So please have a look at the sticky posts at the top of the forum.  Posting DDL and sample data in a useable form will avoid much confusion and Q&A posts.  Typically it is important to know what version of sql server you are using (as well as the edition). 

    Thursday, July 18, 2013 1:07 PM
  • I'm using SQL 2008 R2 (SP2). Some sql code below to recreate my sample data set:

    create table account (
    accountid int not null,
    versionid int not null,
    balance decimal (12,2),
    [date] datetime)
    
    insert into account (versionid, accountid, balance, [date]) values (1,1,1450.22,'01/02/2013')
    insert into account (versionid, accountid, balance, [date]) values (2,1,1450.22,'04/02/2013')
    insert into account (versionid, accountid, balance, [date]) values (3,1,1954.34,'04/02/2013')
    insert into account (versionid, accountid, balance, [date]) values (1,2,3420.00,'06/02/2013')
    insert into account (versionid, accountid, balance, [date]) values (2,2,3420.00,'06/02/2013')
    insert into account (versionid, accountid, balance, [date]) values (3,2,4975.11,'06/02/2013')
    
    ALTER TABLE account ADD  CONSTRAINT [PK_account] PRIMARY KEY CLUSTERED (accountid asc, versionid asc)
    
    

    The query I have is as follows, which works for one specific column. Is there any way to make this dynamic to pick up the other columns in the table (e.g. using a cursor that goes through information_schema.columns) so I wouldn't have to hardcode them in the query?

    SELECT
        [current].accountid, [current].balance, [current].date
    FROM
        account AS [current]
    LEFT OUTER JOIN
        account AS previous
        ON [current].accountid = previous.accountid
        AND [current].versionid = previous.versionid + 1
    WHERE
        [current].balance <> previous.balance
        OR [previous].balance IS NULL
    ORDER BY
        [current].accountid
    

    Thursday, July 18, 2013 5:13 PM