Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 6 down vote 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 ...]
    [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 |
share|improve this answer

USING UNION

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

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 !!!

share|improve this answer
1  
I had a similar issue and UNION was exactly what I was looking for. – Michael Richardson Oct 30 '15 at 18:20
    
@MichaelRichardson glad I could help !!! – RolandoMySQLDBA Oct 30 '15 at 18:21

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".

share|improve this answer

I guess you mean something like this:

SELECT CONCAT(her_name, his_name, other_name) AS name FROM foo
share|improve this answer
1  
no, that will give concatenation what I am looking for is a single column (like in union) the results will be in the same column but not in the same row – igx Oct 14 '14 at 14:28
    
My bad, in that case I don't think there is a more elegant solution for this. Maybe you could create a function, but that gives you more lines of code and that's not worth it if you just want a union all on 3 columns. – Erik van de Ven Oct 14 '14 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.