oracle - SQL Developer pivot with unknown number of values into one column -

i've been trying figure out how use pivot function in sql developer 1 query i'm trying do. didn't find me previous posts hoping me out.

a small section of data need select looks this:

| id | name | country | state | date |  type  | amount | +----+------+---------+-------+------+--------+--------+ |  1 | john | u.s.    | tx    |  345 | red    |     76 | |  1 | john | u.s.    | tx    |  345 | blue   |    357 | |  2 | alex | u.s.    | co    |  654 | red    |    231 | |  2 | alex | u.s.    | co    |  654 | black  |     90 | |  2 | alex | u.s.    | co    |  654 | blue   |    123 | |  2 | alex | u.s.    | co    |  654 | red    |    456 | |  1 | john | u.s.    | tx    |  345 | gold   |     60 | |  1 | john | u.s.    | tx    |  345 | silver |     70 | 

i need have each different type own column above becomes:

| id | name | country | state | date | red | blue | black  | other  | +----+------+---------+-------+------+-----+------+--------+--------+ |  1 | john | u.s.    | tx    |  345 |  76 |  357 | (null) | 130    | |  2 | alex | u.s.    | co    |  654 | 687 |  123 | 90     | (null) | 

in data, don't know how many 'other' colours there are, or (but there's finite number of them)

i appreciate can offer me this.

you did not specify version of oracle using. if using oracle 11g, can use pivot function:

select * (   select id, name, country, state, "date", amount,     case        when type in ('red', 'blue', 'black') type       else 'other' end type   yt )  pivot (   sum(amount)   type in ('red', 'blue', 'black', 'other') ); 

see sql fiddle demo