How to convert row into columns in SQL?

Question

Given a table consisting of one row, I would like to transpose it into a table of one column. So basically, it is the inverse operation from what was asked in this question. I am using MEMSQL, but any SQL-like approaches are welcome.

So if my table looks like:

---------------------------------------------------------------------
| FirstName  |Amount|   PostalCode   |   LastName  |  AccountNumber |
---------------------------------------------------------------------
| John       | 2.4  |   ZH1E4A       |   Fork      |  857685        |
---------------------------------------------------------------------

I am looking to get the following output:

--------------
| anyName    |
--------------
| John       |
| 2.4        |
| JZH1E4Aohn |
| Fork       |
| 857685     |
--------------

EDIT: I am aware that this can be done using union all, but using union all can take lots of time if the row is long. I am looking for the most efficient way.

Answer 1

You want to unpivot the data. The challenge is dealing with the datatypes. You need to convert them all to the same type. Presumably, this only applies to amount and perhaps to accountnumber:

select firstName as anyName from t
union all
select cast(Amount as char) from t
union all
select PostalCode from t
union all
select LastName from t
union all
select cast(AccountNumber as char) from t;

If your table is very large or is really a complicated view, then there are other methods that don't require scanning the table once for each column.

You can also use cross join and case:

select (case when n.n = 1 then firstName
             when n.n = 2 then cast(Amount as char)
             when n.n = 3 then PostalCode
             when n.n = 4 then lastName
             when n.n = 5 then cast(AccountNumber as char)
        end) as anyName
from t cross join
    (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
      ) n