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)
 ;

• Bhuban: Thank you instead of Select * From YourTable t which add two more columns (columnName and Value) to original select statement Select u.columnName, u.Value from YourTable t CROSS Apply(...............); does a perfect job.
• The Outstanding Question Asker: Does not work for me, there is an error indicating SELECT is on the wrong place, apparently....probably something with the syntax. I am using MySQL however.
• Rob Farley: I can’t guarantee this will work in MySQL. The syntax is valid in SQL Server though.