mysql Select values from multiple columns into single column

Question

I have a table in a database that has several columns containing the same sort of data, these values are allowed to be null. I need to select each of the non-null values into a single column of values that care about the identity of the row from which they originated.

So, for a table that looks like this:

+---------+------+--------+------+
|   Id    | name  | v1    | v2   | 
+---------+------+--------+------+
|    1    | eko  | aa     |  bb  |
|    2    | agus | null   |  cc  |
|    3    | eko  | dd     |  null|
|    4    | budi | aa     |  null|
|    5    | siti | ff     |  gg  |
+---------+------+--------+------+

I wish to select each of the values aa,bb,cc, etc into a single column. My result data should look like the following table.

+-------+-------+-------+
| id    | name  | v     |
+-------+-------+-------+
|  1    | eko   | aa    |
|  1    | eko   | bb    |
|  2    | agus  | cc    |
|  3    | eko   | dd    |
|  4    | budi  | aa    |
|  5    | siti  | ff    |
|  5    | siti  | gg    | 
+-------+-------+-------+

I am using mysql. Is there a technique for achieving this with respect to performance too?

Don't you mean | 1 | eko | bb | for the second row in the expected output? — Nae
– Nae, Commented Oct 24, 2018 at 14:15

rich · Accepted Answer · 2018-10-24 14:16:11Z

2

You could just use two queries and use the union statement of the two to append the two sets:

Select id, v1 as v
From table 
where v1 is not null

union all

select id, v2 as v
from table
where v2 is not null

But to make this dynamic (any number of v...) you would have to iterate over the columns. See:mysql, iterate through column names

answered Oct 24, 2018 at 14:16

rich

5707 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql Select values from multiple columns into single column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related