Geeks With Blogs
Nagendra Prasad
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: I tried these methods in SQL SERVER 2005. Some of them might not work in earlier versions.
Posted on Friday, March 13, 2009 12:21 PM | Back to top


Comments on this post: Convert multiple rows into one row - SQL Server

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Thank you for fosting this it helped me big time
Left by Yakup on Jan 23, 2010 9:00 AM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Very useful, thanks
Left by Ping on Feb 02, 2010 11:41 AM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Valeu (thanks... :))

Left by Antonio on Feb 24, 2010 2:04 PM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
thank you ^ ^
Left by natty on Mar 16, 2010 11:36 PM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
my sql query is not working. can you help me?

DECLARE @str VARCHAR(7000)
SELECT
(
SELECT
@str = COALESCE(@str + ', ', '') +
[dish_category]
FROM [tblDishCatCluster]
WHERE [dish_id] = [tblDish].[dish_id]

SELECT @str

) AS [all_category], [tblDish].[dish_name]
FROM [tblDish]
Left by Lee Choon kheng on Jun 15, 2010 6:13 PM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
good advice. clever way of leveraging xml. thank you.
Left by Noah on May 05, 2011 9:42 AM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Very Very thanks man..... But please put where condition also for users.....
Left by M Purna Venkateswarlu on Dec 14, 2011 10:31 PM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
---thanks for providing nice Queryies it is very useful who are starting their carrier in Sql....thanks alot......
DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';' from table FOR XML PATH('')),1,1,'')
SELECT @str
-----------------its not showing first letter of first contact _email please rectify...thanks in Advance
.......................
--this query will work
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';' from table FOR XML PATH('')),1,0,'')

Left by Kirankumar on Dec 15, 2011 3:10 PM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Thank you very much. It helped me lot
Left by Jaikumar on Apr 18, 2012 7:20 AM

# re: Convert multiple rows into one row - SQL Server
Requesting Gravatar...
Thanks a lot. It really Worked Fine.
Left by S.D.Gupta on Mar 31, 2013 12:30 AM

Your comment:
 (will show your gravatar)


Copyright © nagendraprasad | Powered by: GeeksWithBlogs.net | Join free