Queries for faster development

Concatenation of column values :

I want to concatenate one column values and get it as an aggregated result from database,

For Eg :

EMAIL_ADDRESS 

kiran.joshi@darkhorseboa.com
j.kiran809@gmail.com
j.kiran@alahli.com

Result should be something like this

<to><address><emailAddress>kiran.joshi@darkhorseboa.com</address><address><emailAddress>j.kiran809@gmail.com</emailAddress></address><address><emailAddress>j.kiran@alahli.com</emailAddress></address></to>

This can be achieved in SQL Server using STUFF function and the link can be found here

http://learnings.joshikiran.com/2014/10/how-to-use-stuff-keyword-in-microsoft.html

In Oracle it can be achieved by,

SELECT 
  '<to>'||LISTAGG('<address><emailAddress>'||EMAIL_ADDRESS||'</emailAddress></address>','')
    WITHIN GROUP (ORDER BY COLUMN3)||'</to>' AS ALIAS_ID
FROM TABLE1
WHERE COLUMN2 = 'sample' AND EMAIL_ADDRESS IS NOT NULL;

Note : This works for Oracle version 11g R2 and higher only.

Credits : PadmaPriya Iyengar
----------------------------------------------------------------------------------------------------------------------