Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT:

SELECT his_name , her_name, other_name FROM foo;

Bu, I instead I want to combine the results all into a single column.
As an example, I can do this with UNION ALL as

SELECT her_name AS name FROM foo
UNION ALL
SELECT his_name AS name FROM foo
UNION ALL
SELECT other_name AS name FROM foo

Is there more elegant way to do this operation?

It is unclear to me what is a "more elegant way".

Oracle you can use the following statement to make columns to rows

select all_name
from foo
unpivot (all_name for col_name in (
his_name,
her_name,
other_name));


This is the syntax diagram of the select statement

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

Neither the WHERE,GROUP BY, HAVING, LIMIT, SELECT, INTO, FOR UPDATE nor the LOCK IN SHARE MODE clause can increase the number of rows defined by the FROM clause.
So if table_references equals foo the query cannot contains more rows than the table foo.

So MySQL does not have such an "elegant" way to unpivot a table.

A way to do such unpivoting without the use of UNION can be done buy using a join. We want to create 3 rows for each row of the foo table, so we create an auxiliary table containing three rows
and (cross) join it to the foo table. Now we have three rows in our query for each row in the base table foo. Each query row can be filled by the appropriate data. Instead the ELT function one can use IF or CASE.

SQL Fiddle

MySQL 5.6 Schema Setup:

create table foo (
  his_name varchar(10),
  her_name varchar(10), 
  other_name varchar(10));

insert into foo(his_name,her_name,other_name) values ('one','two','three');
insert into foo(his_name,her_name,other_name) values ('four','five','six');

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Pivot Query:

select elt(aux.line,foo.his_name,foo.her_name,foo.other_name) all_name
from foo  cross join  aux

Results:

| all_name |
|----------|
|      one |
|     four |
|      two |
|     five |
|    three |
|      six |

Of course there are different ways to create a table containing the three rows with values 1,2,3:

SQL Fiddle

Using an auxiliary table:

create  table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);

Using an auxiliary table:

select line
from aux

using a constant expression:

select 1 line 
union all 
select 2 
union all 
select 3

counting row numbers:
I found it here

SELECT
 @rownum := @rownum + 1 line
 FROM
(SELECT @rownum := 0) r, INFORMATION_SCHEMA.COLUMNS t
where @rownum<3

using one of the dictionary views:

SELECT
 ordinal_position  line
 from INFORMATION_SCHEMA.COLUMNS t
where table_catalog='def'
and table_schema='information_schema'
and table_name='COLUMNS'
and ordinal_position between 1 and 3

Results:

| ORDINAL_POSITION |
|------------------|
|                1 |
|                2 |
|                3 |

StackExchange Database Administrators Q#80140, answer score: 13