oracle - How do I generate combinations based on column values in SQL? (Is this even possible?) -

i'm trying make new column in table contents based off values of existing pair of columns. if have table like(id not primary key)

id   |   value  |   new column(the column want) 1                   a:apple 1         b           a:orange 2        apple        b:apple 2        orange       b:orange 

im novice sql, insight here helpful.

btw, im using oracle if matters.

additional details: im looking pair values:values based on fact id's dont match

assuming want values id1 paired values id2, can cross-join table itself, filtered on id:

select t1.value ||':'|| t2.value my_table t1 cross join my_table t2 t1.id = 1 , t2.id = 2; 

sql fiddle.