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

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 |

USING UNION

Others have submitted answers trying aggregation to collect data without using UNION

  • May 12, 2014 : Query improvements without UNION
  • May 05, 2015 : Calculating values from three related tables, without using join or union
  • Feb 20, 2012 : SQL Data aggregation

In this instance, what makes UNION an absolute must is the merging of three columns into a single column. Nothing could be more elegant that using UNION

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

This would produce a distinct list of names.

If the intent is to select some columns and generate a single column list of the distinct values, then Dynamic SQL is needed to produce such a query. For this example, let's suppose the following

  • mydb schema
  • table called foo
  • columns to select together
    • her_name
    • his_name
    • other_name

Here is the Dynamic SQL to generate the same query

USE mydb
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(
    CONCAT('SELECT ',column_name,' AS name FROM ',table_name)
    SEPARATOR ' UNION ')
INTO @sql
FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name='foo' AND column_name IN
('her_name','his_name','other_name');
SELECT @sql\G
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPEARE s;

The SELECT @sql\G will let you see the generated SQL

Without formally scripting a Stored Procedure, this is as good as it is going to get.

WITHOUT USING UNION

If the goal is to strictly not to use UNION, you need a temp table with a PRIMARY KEY:

CREATE TABLE names SELECT her_name name FROM foo WHERE 1=2;
ALTER TABLE names ADD PRIMARY KEY (name);
INSERT IGNORE INTO names (name) SELECT her_name FROM foo;
INSERT IGNORE INTO names (name) SELECT his_name FROM foo;
INSERT IGNORE INTO names (name) SELECT other_name FROM foo;
SELECT * FROM names;
DROP TABLE names;

LOOK MOM, NO UNION !!!


A semi-solution: They will be in column. (But they are also in a single row, sort of.)

SELECT CONCAT_WS("\n", her_name, his_name, other_name) AS name FROM foo

For example:

mysql> SELECT CONCAT_WS("\n", province) FROM Provinces;
+---------------------------+
| CONCAT_WS("\n", province) |
+---------------------------+
| Alberta                   |
| British Columbia          |
| Manitoba                  |
| New Brunswick             |
| Newfoundland and Labrador |
| Nova Scotia               |
...

If it is going to a web page, then consider "<br>" instead of "\n".

Tags:

Mysql

Union

Recent Posts