title: Query to join two tables and to use LISTAGG function
body: <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>