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.
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
| 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:
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
| ORDINAL_POSITION |
|------------------|
| 1 |
| 2 |
| 3 |
Others have submitted answers trying aggregation to collect data without using UNION
May 12, 2014 : Query improvements without UNIONMay 05, 2015 : Calculating values from three related tables, without using join or unionFeb 20, 2012 : SQL Data aggregationIn 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 schemafooher_namehis_nameother_nameHere 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.
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;
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".
For MariaDB users: there are virtual sequence tables containing sequence of integers. No need to create temporary / auxiliary table with those integers!
Cross join (Cartesian product) on such table and use ELT to extract n-th column!
Solution:
SELECT ELT(seq_1_to_3.seq, foo.his_name, foo.her_name, foo.other_name) AS name
FROM foo
CROSS JOIN seq_1_to_3;
Explanation:
Virtual table seq_1_to_3 has 1 column seq and contains 3 integers in that column: 1, 2, and 3.
In this query we go through each row in table foo 3 times (because of the cross-join with virtual table seq_1_to_3) and pick only one seq-th column from the given list of columns: his_name, her_name, other_name.
