Why have you included 'E' as consonant? What do you want to show in the vowel column for the consonants after G?
Anyway, you could do this by pivoting the results. You'll need to give a number to each vowel and consonant for this to work (A = 1, B = 1, E = 2, C = 2, etc.):
create table table_a ( id int, vowel_cons varchar2(1));
insert into table_a values (1, 'A');
insert into table_a values (1, 'B');
insert into table_a values (1, 'C');
insert into table_a values (1, 'D');
insert into table_a values (1, 'E');
insert into table_a values (1, 'F');
insert into table_a values (1, 'G');
insert into table_a values (1, 'H');
insert into table_a values (1, 'I');
insert into table_a values (1, 'O');
insert into table_a values (1, 'U');
select id, vowel_vc, cons_vc
from (
select id,
VOWEL_CONS,
case
when vowel_cons in ( 'A','E','I','O','U' ) then 'V'
else 'C'
end as tp,
row_number() over (partition by case
when vowel_cons in ( 'A','E','I','O','U' ) then 'V'
else 'C'
end order by vowel_cons) rn
from table_a
)
pivot (
min(vowel_cons) as vc for (tp) in ('V' as vowel, 'C' as cons)
)
order by rn;
ID V C
---------- - -
1 A B
1 E C
1 I D
1 O F
1 U G
1 HAs you say you're using 10g, here's the old-style way of doing it:
select id,
max(decode(tp, 'V', vowel_cons)) v,
max(decode(tp, 'C', vowel_cons)) c
from (
select id,
VOWEL_CONS,
case
when vowel_cons in ( 'A','E','I','O','U' ) then 'V'
else 'C'
end as tp,
row_number() over (partition by case
when vowel_cons in ( 'A','E','I','O','U' ) then 'V'
else 'C'
end order by vowel_cons) rn
from table_a
)
group by id, rn
order by rn;
ID V C
---------- - -
1 A B
1 E C
1 I D
1 O F
1 U G
1 H