Query to join two tables and to use LISTAGG function
<p>I have two tables</p>

<pre>
price
--------

id      value   key
sku1    r1      12  
sku1    r2      13  
sku2    r1      12
sku3    r1      14

specialprice
-----

id      value   key startdate   enddate
sku2    r2      13  20-Mar      20-May
sku3    r1      15  20-Mar      20-May
</pre>

<p>I am trying to use LISTAGG function and bring output like:</p>

<pre>
output
------
skuid   value
sku1    r1:12,r2:13
sku2    r2:13
sku3    r1:15
</pre>

<p>If there is an entry in specialprice table and sysdate is between startdate and enddate then i need to consider that otherwise i need to get value from price table.
In price table a id may have two entries i need to LISTAGG those two records.
specialprice table may not have entry for the id in price table. So i am using FULL OUTER JOIN</p>

<p>PFB Query which i constructed:</p>

<pre><code>SELECT
  a.id,
  listagg (a.value | | ':' | | nvl (a.key, ' '), ',') WITHIN GROUP (ORDER BY a.value) AS price
FROM price a 
FULL OUTER JOIN specialprice b
  ON(b.id = a.id)
WHERE sysdate between b.startdate and b.enddate
GROUP BY a.id
</code></pre>

<p>but issue is i am not able to get the specialprice table value and key joined.
Please help.</p>