SQL Convert Top 1 row results to columns

Question

I have table with 10 columns and my query will give me only 1 record always. Now i want to convert this single record to key value pair.

Ex: SELECT * FROM dbo.Table OUTPUT:

col1  col2  col3  col4 col5
============================ 
val1 val2   val2  val4 val5

Expecting ouput as follows

name    value  
==============
col1     val1
col2     val2
col3     val3
col4     val4
col5     val5

Please suggest how to do this.

Many Thanks

Answer 1

Assuming that your columns are a) Fixed and b) of the same type, you can use UNPIVOT to achieve this translation:

select name, value
from MyTable
unpivot
(
  name
  for Value in ([Col1], [Col2], [Col3], [Col4], [Col5])
) x;

SqlFiddle example here