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

accepted +50

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 ...] [FROMtable_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 tablereferencesequals 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 auxiliarytable: 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 |