Table 1
ID |COL2|COL3|COL4|COL5|
ROW1| 1 | 1 | 1 | 1 |
ROW2| 1 | 3 | 2 | 6 |
ROW3| 1 | 1 | 1 | 1 |
ROW4| 1 | 1 | 1 | 1 |
ROW5| 1 | 1 | 1 | 1 |
ROW6| 1 | 1 | 1 | 1 |
Problem
After updating table 1, I have a new row with changed values:
ID |COL2|COL3|COL4|COL5| ROW1| 1 | 1 | 1 | 1 | ROW2| 1 | 0 | 4 | 5 | ROW3| 1 | 1 | 1 | 1 | ROW4| 1 | 1 | 1 | 1 | ROW5| 1 | 1 | 1 | 1 | ROW6| 1 | 1 | 1 | 1 |
I want to select the row from table 1 that was changed into multiple rows based on the column name.
Desired output
ID |OLD|NEW|
ROW2| 3 | 0 |
ROW2| 2 | 4 |
ROW2| 6 | 5 |
My attempt
I have created a trigger like:
select d.col2 as old, i.col2 as new
from inserted i
inner join deleted d
on i.id = d. id
where not exists
(
select i.row2
intersect
select d.row2
)
...but it just returns one row.
How I can I achieve the output I want? I am using SQL Server 2008.