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 | [email protected] | 1004 Court Street | USA |
Antone Clay | [email protected] | 6219 Devon Court | UK |
Issac Luigi | [email protected] | 43 Bridge Street | USA |
Lucio Andrea | [email protected] | 2283 Laurel Lane | Italy |
... | ... | ... | ... |
View:
field | value |
--------------|-------------------|
name | Al Wade Avery |
email | [email protected] |
address | 1004 Court Street |
country | USA |
... | ... |
name | Antone Clay |
email | [email protected] |
address | 6219 Devon Court |
country | UK |
... | ... |
name | Issac Luigi |
email | [email protected] |
address | 43 Bridge Street |
country | USA |
... | ... |
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.