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