Friday, February 19, 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: I tried these methods in SQL SERVER 2005. Some of them might not work in earlier versions.

5 comments:

  1. It is appropriate to think of this tea in terms of anti-aging and a long and healthy life.
    Cayenne, a natural phytonutrient derived from capsicum, helps support
    circulation. The term functional food is used in Europe for foods that can improve our state of health or reduce the
    risk of diseases.

    my website; Chris Ashenden [source]

    ReplyDelete
  2. Conceived in 2004 by Yuhki Kamatani, Nabari no Ou has all the ingredients for a successful series, however with the release of volume 4,
    are they only now coming together. Go ahead and explore the
    game in its entirety first, then if you are not satisfied with
    what you've bought, get more later. " Machinima's Nanea Reeves offered the mention, though Pascal Brochier E.

    my site; coc on pc

    ReplyDelete
  3. Good post. I learn something new and challenging on blogs I
    stumbleupon everyday. It will always be exciting to read content from other authors and practice something from other websites.



    Also visit my blog - Click here

    ReplyDelete