RSS

Convert multiple rows into one row – SQL Server

07 Jun

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;

Referenced by: http://geekswithblogs.net/nagendraprasad/archive/2009/03/13/convert-multiple-rows-into-one-row—sql-server.aspx

 

 

 

 

Advertisements
 
4 Comments

Posted by on June 7, 2011 in SQL Server

 

4 responses to “Convert multiple rows into one row – SQL Server

  1. dating

    September 22, 2013 at 5:01 AM

    Awesome blog! Do you have any hints for aspiring writers? I’m hoping to start my own blog soon but I’m a little lost on everything.
    Would you recommend starting with a free platform like WordPress or
    go for a pqid option? There are so many choices out there that I’m totally
    overwhelmed .. Any suggestions? Bless you!

     
  2. party ideas app

    September 25, 2013 at 11:01 AM

    WOW just what I was searching for. Came here by searching for best
    bachelorette destinations

     
  3. работа тестером онлайн игр

    September 25, 2013 at 12:29 PM

    Great site you have here but I was curious about if you
    knew of any message boards that cover the same topics discussed in
    this article? I’d really like to be a part of group where I can get responses from other experienced individuals that share
    the same interest. If you have any suggestions, please let me know.
    Appreciate it!

     
  4. ginecologie

    October 1, 2013 at 11:33 AM

    It’s amazing to go to see this web site and reading the
    views of all friends about this paragraph, while I am also zealous of getting
    familiarity.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: