tuliomarchetto tuliomarchetto - 3 months ago 10
MySQL Question

How create a 2 columns SQL View?

I need to create a SQL View (using MySQL Workbench) that should simply take all the values of a row and stack them with theirs respectives column names on the first column. Like this:

Original Table:

name | email | address | country | ...
------------------|----------------------|--------------------|------------|
Al Wade Avery | wade@hotmail.com | 1004 Court Street | USA |
Antone Clay | aclay@gmail.com | 6219 Devon Court | UK |
Issac Luigi | issac@hotmail.com | 43 Bridge Street | USA |
Lucio Andrea | lucioandrea@me.com | 2283 Laurel Lane | Italy |
... | ... | ... | ... |


View:

field | value |
--------------|-------------------|
name | Al Wade Avery |
email | wade@hotmail.com |
address | 1004 Court Street |
country | USA |
... | ... |
name | Antone Clay |
email | aclay@gmail.com |
address | 6219 Devon Court |
country | UK |
... | ... |
name | Issac Luigi |
email | issac@hotmail.com |
address | 43 Bridge Street |
country | USA |
... | ... |

Answer

A simple way to do this is using union all:

create view v_table as
    select 'name' as field, name as value from t
    union all
    select 'email', email
    union all
    select 'address', address
    union all
    select 'country', country;

However, you don't really want to do this because you are losing information about which fields are tied to which row in the original data. If you have an id field, you should include that in the view:

create view v_table as
    select id, 'name' as field, name as value from t
    union all
    select id, 'email', email
    union all
    select id, 'address', address
    union all
    select id, 'country', country;

Otherwise, another unique column -- perhaps name -- should be included.

Comments