Thursday, May 6, 2010

Convert multiple rows into one row - SQL Server


As I need to send email to many people, i need to convert multiple emails into a single row delimited by semi-colon(;), i had lots of solutions, but which is an old type of solution which needs more lines of code. As i want to use one or two line code which would resolve, i found three methods for my solution which is very simple.

Method 1:

DECLARE @str varchar(4000)
SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str


Method 2:

DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + CONTACT_EMAIL FROM table
SELECT @str


Method 3:

DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';'   from table FOR XML PATH('')),1,1,'')
SELECT @str


Multiple rows returned:

CONTACT_EMAIL
abc1@domain.com
abc2@domain.com
abc3@domain.com

 3 row(s) affected.

After executing one of the methods, i got the result as

CONTACT_EMAIL
abc1@domain.com;abc2@domain.com;abc3@domain.com;

1 row(s) affected.


NOTE: These methods in SQL SERVER 2005. Some of them might not work in earlier versions.

No comments:

Post a Comment