Search code examples
sqlunionsybasesap-ase

Result set with triple union

lets say I have a simplified table with 2 Columns:

+------+------+
| Col1 | Col2 |
+------+------+
| a    | b    |
| a    | c    |
| b    | d    |
+------+------+

Now, I want a result set where I can wirte my header in line 1 and all distinct values of Col1and Col2 in the follwing lines like this:

Header
a
b
c
d

while it works fine with only one Column:

select 'Header'
union
select Col1
from table

suprisingly it does not work with more than one union:

select 'Header'
union
select Col1
union
select Col2
from table

fails. Who knows why and can tell me how to achieve what I want?

Thanks very much!

Solution

  • The problem is that union does not guarantee ordering. Try this:

    select col
from (select 'Header' as col, 0 as ordering
      union
      select Col1, 1
      from table
      union
      select Col2, 1
      from table
     ) t
order by ordering;