title: SQL Developer pivot with unknown number of values into one column
body: <p>I've been trying to figure out how to use the pivot function in SQL Developer for this one query I'm trying to do. I didn't find anything to really help me from previous posts so I was hoping someone could help me out.</p>
  
  <p>A small section of the data that I need to select currently looks like this:</p>
  
  <pre><code>| 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 |
  </code></pre>
  
  <p>I need to have each different type as its own column so that the above becomes:</p>
  
  <pre><code>| 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) |
  </code></pre>
  
  <p>In my data, I don't know how many 'Other' colours there are, or what they all are (but there's definitely a finite number of them)</p>
  
  <p>I appreciate any help anyone can offer me with this.</p>