I'm using SQL Server 2005.
I have a table that has an archive of rows each time some field was changed. I have to produce a report that displays fields that were changed for each employee.
My table schema:
tblEmp(empid, name, salary, createddate)
Row 1: 1, peter, 1000, 11/4/2012
Row 2: 1, peter, 2000, 11/5/2012
Row 3: 1, pete, 2000, 11/6/2012
Row 4: 1, peter, 4000, 11/7/2012
1, oldsalary: 1000 newsalary: 2000 (changed on 11/5/2012)
1, oldname: peter newname: pete (changed on 11/6/2012)
1, oldname: pete newname: peter, oldsalary:2000, newsalary: 4000 (changed on 11/7/2012)
WITH cte AS ( SELECT empid, name, salary, rn=ROW_NUMBER()OVER(PARTITION BY empid ORDER BY createddate) FROM tblemp ) SELECT oldname=CASE WHEN c1.Name=c2.Name THEN '' ELSE C1.Name END, newname=CASE WHEN c1.Name=c2.Name THEN '' ELSE C2.Name END, oldsalary=CASE WHEN c1.salary=c2.salary THEN NULL ELSE C1.salary END, newsalary=CASE WHEN c1.salary=c2.salary THEN NULL ELSE C2.salary END FROM cte c1 INNER JOIN cte c2 ON c1.empid=c2.empid AND c2.RN=c1.RN + 1