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
----------------------------------------------------------------------------------------------------------------------

How to use STUFF keyword in Microsoft SQL Server

Usage of STUFF in Microsoft SQL Server 

General syntax of STUFF in SQL Server is 

STUFF ( character_expression , start , length , replaceWith_expression )

You can refer the details from below link
http://msdn.microsoft.com/en-IN/library/ms188043.aspx

SELECT DISTINCT COMMISION_NUMBERS = 
STUFF((Select ','+COMMISION_NUMBER from MARS_ALLOCATION_DATA where MONTH='JUN' and YEAR=2014
 FOR XML PATH('')),1,1,'') FROM MARS_ALLOCATION_DATA