2

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?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
nekstak
  • 27
  • 1
  • 7

1 Answers1

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

rich
  • 520
  • 6
  • 21