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.

Gordon Linoff · Accepted Answer · 2020-05-14T18:20:29.950

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

Thank you @GordonLinoff, indeed my case is one where I need a more efficient method than `union all`. Can you please elaborate on the alternative methods? — pyrubjav, May 14 '20 at 16:34

score 0 · Answer 2 · answered May 14 '20 at 07:07

0

You can use union all, you may need to convert integer values to string.

select firstName as anyName from yourTable
union all
select Amount from yourTable
union all
select PostalCode from yourTable
union all
select LastName from yourTable
union all
select AccountNumber from yourTable

answered May 14 '20 at 07:07

zealous

7,336
4
16
36

Thanks, I am aware of this method, but union is not very efficient. I was looking to do this in a more efficient way, as this row can be long. I will add this in an edit – pyrubjav May 14 '20 at 08:04

score 0 · Answer 3 · answered May 20 '20 at 19:01

With my experience in memSQL I would suggest writing a transformation script to insert into a target table each column at a time (query information schema and then iterate over it pushing an insert into the target each iteration). MemSQL seems to have issues with complex SQL... especially the flavor of adhoc queries you run when figuring things out during development such as what it looks like you're doing... of course their marketing folks will tell you otherwise and as they pull cash out of investors/customers the functionality will be added so maybe in a year or two it won't barf on stacked unions, but I recommend avoiding them beyond a couple and definitely avoid if it isn't a simple select all union.

How to convert row into columns in SQL?

3 Answers3