SQL Compare Rows From the Same Table Using Unpivot
Filed under SQL , SQL Server , T-SQL
I was given the task of comparing 2 rows in one table. In this case it was a history type table that had all the data displayed in each row even if the column did not have a change from the previous record. The table records had to be compared with the previous record and that could be determined by a datatime field. Furthermore, the database being dealt with is SQL Server 2005. Alright, enough with the background of the problem and onto my solution.
After doing a little researching I read about 2 new functions released with SQL Server 2005, PIVOT and UNPIVOT. Basically you can rotate a column into rows and the opposite. For a complete explanation go here: http://msdn.microsoft.com/en-us/library/ms177410.aspx
Using the SQL Server UNPIVOT function I rotated the results a little.
ORIGINAL OUTPUT
SELECT PK, ID, DateTime, A, B, C, D
FROM tblExample
| PK |
ID |
DateTime Created |
A |
B |
C |
D |
| 1 |
1 |
6/14/2010 | Hello |
56 |
40 |
BLAH |
| 2 |
1 |
6/15/2010 | Hello |
56 |
45.7 |
BLAH |
| 3 |
1 |
6/16/2010 | Hello |
57 |
45.7 |
BLAH |
| 4 |
1 |
6/17/2010 | Good Bye |
57 |
45.7 |
BLAH |
AFTER UNPIVOT
SELECT ID, DateTime, colname, colvalue
FROM (
SELECT PK,
ID,
DateTime,
CAST(A as VARCHAR(100)) AS A,
CAST(B as VARCHAR(100)) AS B,
CAST(C as VARCHAR(100)) AS C,
CAST(D as VARCHAR(100)) AS D
FROM tblExample
UNPIVOT
(colvalue FOR colname IN(A, B, C, D)) test
| ID |
DateTime Created |
Column Name |
Value |
| 1 |
6/14/2010 |
A |
Hello |
| 1 |
6/14/2010 |
B |
56 |
| 1 |
6/14/2010 |
C |
40 |
| 1 |
6/14/2010 |
D |
BLAH |
| 1 |
6/15/2010 |
A |
Hello |
| 1 |
6/15/2010 |
B |
56 |
| 1 |
6/15/2010 |
C |
45.7 |
| 1 |
6/15/2010 |
D |
BLAH |
| 1 |
6/16/2010 |
A |
Hello |
| 1 |
6/16/2010 |
B |
57 |
| 1 |
6/16/2010 |
C |
45.7 |
| 1 |
6/16/2010 |
D |
BLAH |
| 1 |
6/17/2010 |
A |
Good Bye |
| 1 |
6/17/2010 |
B |
57 |
| 1 |
6/17/2010 |
C |
45.7 |
| 1 |
6/17/2010 |
D |
BLAH |
It is important to note that you can only UNPIVOT with columns of the EXACT same datatype. For this reason you will notice that I had to CAST all the columns to VARCHAR(100) for this to work (NOTE: If you have column of VARCHAR(3) and one of VARCHAR(4) it will not work. They have to be EXACT from my understanding).
Now with the data turned around a little we can JOIN the QUERY with itself find out what columns have been changed from the previous datetime entry (NOTE: I would not trust comparing the columns using Incrementing INT Primary Keys. This should always be done with a datetime. I personally like UID primary keys anyways).
SELF JOIN QUERY
SELECT a.ID, a.DateTime, a.colname, b.colvalue as OldValue, a.colvalue as NewValue
FROM (
SELECT ID, DateTime, colname, colvalue
FROM (
SELECT PK,
ID,
DateTime,
CAST(A as VARCHAR(100)) AS A,
CAST(B
as VARCHAR(100)) AS B,
CAST(C as VARCHAR(100)) AS C,
CAST(D as
VARCHAR(100)) AS D
FROM tblExample
UNPIVOT
(colvalue FOR colname IN(A, B, C, D)) unpivot) a
INNER JOIN(
SELECT ID, DateTime, colname, colvalue
FROM (
SELECT PK,
ID,
DateTime,
CAST(A as VARCHAR(100)) AS A,
CAST(B
as VARCHAR(100)) AS B,
CAST(C as VARCHAR(100)) AS C,
CAST(D as
VARCHAR(100)) AS D
FROM tblExample
UNPIVOT
(colvalue FOR colname IN(A, B, C, D)) unpivot) b
ON a.ID = b.ID
AND a.colname = b.colname
WHERE a.colvalue <> b.colvalue
AND b.datetime = (SELECT MAX(sub.DateTime)
FROM ((SELECT ID, DateTime, colname, colvalue
FROM (
SELECT PK,
ID,
DateTime,
CAST(A as
VARCHAR(100)) AS A,
CAST(B
as VARCHAR(100)) AS B,
CAST(C as VARCHAR(100)) AS C,
CAST(D as
VARCHAR(100)) AS D
FROM tblExample
UNPIVOT
(colvalue FOR colname IN(A, B, C, D)) unpivot) )sub
WHERE sub.DateTime � a.DateTime
AND sub.colname = a.colname
AND sub.ID = a.ID)
| ID |
DateTime Created |
Column Name |
Old Value |
New Value |
| 1 |
6/15/2010 |
C |
40 |
45.7 |
| 1 |
6/16/2010 |
B |
56 |
57 |
| 1 |
6/17/2010 |
A |
Hello |
Good Bye |
As you can see the self query does all the comparing for you. You select the values and compare them against the next value of the same ID by using the MAX(DateTime) is was created in the database. This was a very useful way for me to compare records in a history type table. If someone has an easier way to accomplish this task I would love to know.
Jun15