Need T-SQL help in Sql Server 2005

Question

I have two tables Employee and EmployeeLog (with the same columns except LogCreatedDate)

Columns are something like this:

EmployeeID, EmployeeName, LastModifiedBy, ModifiedDate,
ColumnA, ColumnB, ColumnC bla-bla-bla

I need T-SQL which give me output as shown in this table.

ResultSet 1:

On every Employee table update I insert a new row in EmployeeLog table with current values.

EmployeeLog table contains multiple rows for each EmployeeID so I need previous and current value in column that is modified.

So my first step is that I fetch TOP 2 record order by LogCreatedDate DESC. Now what should I do for getting output as shown in table?

ResultSet 2:

Thanks

Answer 1

Try this:

DECLARE @EmployeeLog TABLE
(
    EmployeeID int,
    EmployeeName varchar(50),
    LastModifiedBy varchar(50),
    ModifiedDate datetime,
    ColumnA varchar(50),
    ColumnB varchar(50),
    ColumnC varchar(50),
    LogCreatedDate datetime
)

INSERT INTO @EmployeeLog VALUES (1, 'A', 'A', '2012-02-07', 'A', 'B', 'C', '2012-02-07')
INSERT INTO @EmployeeLog VALUES (1, 'B', 'B', '2012-02-06', 'AA', 'BB', 'CC', '2012-02-06')

;
WITH    Basics AS
(
        SELECT  *, ROW_NUMBER() OVER (ORDER BY LogCreatedDate DESC) as Entry
        FROM    @EmployeeLog 
),      PivotTable AS
(
        SELECT  EmployeeID,
                LastModifiedBy,
                ModifiedDate,
                'ColumnA' ColumnName,
                ColumnA ColumnValue,
                LogCreatedDate,
                Entry
        FROM Basics
        UNION ALL
        SELECT  EmployeeID,
                LastModifiedBy,
                ModifiedDate,
                'ColumnB' ColumnName,
                ColumnB ColumnValue,
                LogCreatedDate,
                Entry
        FROM Basics
        UNION ALL
        SELECT  EmployeeID,
                LastModifiedBy,
                ModifiedDate,
                'ColumnC' ColumnName,
                ColumnC ColumnValue,
                LogCreatedDate,
                Entry
        FROM Basics
)
SELECT  [Current].LastModifiedBy, [Current].ModifiedDate,
        Previous.EmployeeID, Previous.ColumnName,
        Previous.ColumnValue [Previous Value],
        [Current].ColumnValue [Current Value]
FROM    PivotTable Previous
JOIN    PivotTable [Current]
    ON  Previous.EmployeeID = [Current].EmployeeID AND
        Previous.ColumnName = [Current].ColumnName AND
        Previous.Entry = [Current].Entry + 1