SQL 2005: Unpivot

SELECT recordID, oldColumnNames , newValue
FROM (SELECT recordID, [column1], [column2], [column3] FROM Table) AS p
UNPIVOT (newValue FOR oldColumnNames IN ([column1], [column2], [column3])) AS s

ok this is a little weird bear with me.

its going to take column1, 2, 3 and combine their data into one column. It will create a new column which I've called oldColumnNames and the old column name will be value of the oldColumnNames column.

so

id, column1, column2, column3
1, a, b, c
2, d, e, f

becomes
id, oldColumnNames, newValue
1, column1, a
1, column2, b
1, column3, c
2, column1, d
2, column2, e
2, column3, f