Transpose multiple columns into 2 column and single row to multiple row in sql

Question

Using Select top 1 statement I have to retrieve data from an 8 column table without any unique key. Now I want to transpose this table into two columns: original column name to the first and its respective value to second.

An original table like this:

Column1 | Column2 | Column3 | Column4 
55      | 108     | 555     | 85

And the expected result:

ColumnName | Value
Column1    | 55
Column2    | 108
Column3    | 555
Column4    | 85

Answer 1

There's a T-SQL construct called UNPIVOT which is designed for this. But in my experience, you'll have more luck with APPLY as per my code below:

SELECT *
FROM YourTable t
CROSS APPLY
    (SELECT 'Col1', Column1
    UNION ALL
    SELECT 'Col2', Column2
    UNION ALL
    SELECT 'Col3', Column3
    UNION ALL
    SELECT 'Col4', Column4
   ) u (columnName, Value)
  ;