1

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
0

1 Answer 1

5

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)
  ;
3
  • 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.
    – Bhuban
    Sep 15, 2015 at 17:12
  • 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. Jul 24, 2019 at 13:04
  • I can’t guarantee this will work in MySQL. The syntax is valid in SQL Server though.
    – Rob Farley
    Jul 25, 2019 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.