Oracle SQL combine table and pivot in version 9i
<p>I'm trying to find a way to combine 2 tables and pivot.  The caveat is that I'm on oracle version 9i which apparently doesn't have the pivot function.  So I'm hoping there's a hacky way I can get around this.  I have 2 tables with the following form:</p>

<p>person table:</p>

<pre><code>ID  NAME
 1  John
 2  Steve
 3  Bob
 4  Gary
</code></pre>

<p>phone number table:</p>

<pre><code>ID TYPE NUMBER
 1   1  555-5555
 2   1  555-5556
 2   2  555-5557
 3   2  555-5558
 4   5  555-5559
</code></pre>

<p>And I want to be able to join the tables as follows:</p>

<pre><code>ID NAME  NUMBER_1  NUMBER_2
 1 John  555-5555  
 2 Steve 555-5556  555-5557
 3 Bob             555-5558
 4 Gary
</code></pre>

<p>Note that there are a number of different <code>TYPE</code> values but I only care about 1 and 2.</p>

<p>The current solution I have now is to join the tables on <code>ID</code> and use a CASE statement conditioning on <code>TYPE</code> to generate <code>NUMBER_1</code> and <code>NUMBER_2</code>.  The issue is that if I join the tables then there will be 2 rows for Steve.</p>