How to Use COLLECT with VARCHAR2 Oracle 10g
<p>I'm trying to get the COLLECT function to work for me.  I'm using 10g and therefore found that LISTAGG and WM_CONCAT will not work (invalid identifier errors). The data I have is for example as follows. </p>

<pre><code>Order  Lot
123    A23088
123    A23089
089    AABBCC
305    120848
305    CCDDYY
</code></pre>

<p>What I need returned is as follows</p>

<pre><code>Order   Lot
123     A23088, A23089
089     AABBCC
305     120848, CCDDYY
</code></pre>

<p>Using the following, I get the error: TO_STRING is an invalid identifier</p>

<pre><code>TO_STRING ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT
</code></pre>

<p>Using the following, I get the error: expected CHAR "inconsistent datatypes: expected %s got %s"</p>

<pre><code>TO_CHAR ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT
</code></pre>

<p>Using the following, I get the error: expected NUMBER "inconsistent datatypes: expected %s got %s"</p>

<pre><code>COLLECT(DISTINCT WHSE_LOT)
</code></pre>

<p>Is there any way to get this function to work for me? </p>