declare @t table (rowid int, col1 int, col2 int, col3 int)
insert @t values (1,1,2,3)
insert @t values (2,1,2,3)

select	 rowid
	,val = case 
		when n.number = 1 then t.col1
		when n.number = 2 then t.col2
		when n.number = 3 then t.col3 
		end
from	@t t
cross join master..spt_values n
where n.type = 'p'
and	n.number > 0
and	n.number < 4


output:
rowid       val         
----------- ----------- 
1           1
1           2
1           3
2           1
2           2
2           3