NULLIF(HistoryTable.Col1 = MainTable.Col1)
GOIF OBJECT_ID('TempDB..#OrginalTable','U') IS NOT NULL DROP TABLE #OrginalTable IF OBJECT_ID('TempDB..#HistoryTable','U') IS NOT NULL DROP TABLE #HistoryTable CREATE TABLE #OrginalTable( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, A CHAR(1), B CHAR(1), C CHAR(1), D CHAR(1), E CHAR(1))CREATE TABLE #HistoryTable( Change NVARCHAR(10), DateValue SMALLDATETIME, ID INT, A CHAR(1), B CHAR(1), C CHAR(1), D CHAR(1), E CHAR(1), PRIMARY KEY (DateValue,ID)) --- SOME SAMPLE DATA FINAL STATEINSERT INTO #OrginalTableSELECT '1','b','c','R','e' UNION ALLSELECT '0','Y','W','Z','9' --- SAMPLE TRIGGER DATAINSERT INTO #HistoryTableSELECT 'UPDATE','03/20/2013',1,'a','b','c','d','e' UNION ALL -- Row 1 - OrignalSELECT 'UPDATE','03/21/2013',1,'1','b','c','d','e' UNION ALL -- Row 1 - After 1st change SELECT 'UPDATE','03/20/2013',2,'X','Y','W','Z','Z' UNION ALL -- Row 2 - OrignalSELECT 'UPDATE','03/21/2013',2,'0','Y','W','Z','Z' -- Row 2 - After 1st change *EDITED* - Something similar to this, but only the first line complete, and the other lines, only show the values that are different from the previous line... In this sample the second row, only the "visible" the d value, in the third row the a value...SELECT [DateValue] = GETDATE(), [Type] = 'Final',A ,B ,C ,D ,E FROM #OrginalTable WHERE ID = 1 UNION ALLSELECT DateValue , Change , A ,B ,C ,D ,E FROM #HistoryTable WHERE ID = 1 ORDER BY DateValue DESC
WITH CTE_History AS ( SELECT ROW_NUMBER() OVER(ORDER BY DateValue DESC) AS RowNum,* FROM #HistoryTable WHERE ID = 1)