How can we concatenate (, seperated) multiple results (email addrs) from a result set and assign to a variable in SQL

Question

0.00/5 (No votes)

See more:

Hello
I have an SQL spd (SQLServer 2012) in which I have a concatenated list of email addresses as follows

SET @emailAddr = @Email1 + "," +@Email2

@Email1 and @Email are stored with email addresses (values such as abc@gmail.com etc) which was fetched directly from the user details table and each of them contains only single email address values.

Email1 and Email2 are declared as VARCHAR
and emailAddr is declared as NVARCHAR

I have another sql query in the same spd which joins multiple tables to fetch another set of email addresses for multiple persons mentioned in a multi person data field of a form . Query looks like follows (Edited query to remove actual references)
***********************************************
select a.email from Db1.User b, Db1.formfields c, DB1.Userdetails a, DB1.FormRecordmapping d
where b.UserAddressid= a.id
and d.formfieldid= c.id
and b.Userid= d.FormValue
and c.id='3000'
and d.Recordid= xxxx
*************************************************

From the above query we get multiple email addresses depending on the persons selected for the data field
Sample output looks like
----------------------------
abc@gmail.com
dd&ee@hotmail.com
chd123@yahoo.com
----------------------------
My requirement is to format the above 3 email results as comma separated values and concatenate it to the existing list of above variable emailAddr as shown in the beginning.

How can this be achieved in a simpler way?. What changes can I make in the above query to achieve this?

Thanks for your help.

What I have tried:

I have tried using listagg and string_agg functions but it is not supported in SQL Server 2012

Posted 26-May-21 23:01pm

John George 2021

Updated 27-May-21 22:48pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-05-26T23:19:00

Solution 1

Basically, don't.
When you start storing data as CSV in a DB, you start giving yourself a lot of problems later on because SQL string handling is charitably described as "poor".

Instead use a separate table to hold the collection, or to link multiple addresses to a single row in a different table.

If you are processing the addresses locally to SQL, then a temporary table my be what you need. If not, either a SELECT from a "proper" table or a temporary table can be returned to your presentation language where it can be processed much more simply.

Posted 26-May-21 23:19pm

OriginalGriff

Comments

John George 2021 27-May-21 7:29am

Hello we are not storing the emailAddr variable values in table anywhere we just want those values to be passed on to a a service for sending notifications. the service will connect to the spd and process it and will send notifications to the email addresses stored in the emailAddr variable.

OriginalGriff 27-May-21 9:21am

Doesn't matter - have SQL return it as a "standard collection" and teh service can do what it wants with it.
What the service does is immaterial to SQL: it just serves up the data. Technically, it doesn't care that they are email addresses - they are just string fields. Using that string data is a service function, not SQL - and your way requires that the service "unpacks" the SQL CSV data and hopes like heck that they are valid email addresses that don't contain commas.
Has your data been validated on entry to ensure it complies with email address rules? Do you really trust it that much?

John George 2021 27-May-21 10:26am

Dear
The requirement is that based on an event/ activity for a particular record (form ) a notification has to be sent to a set of email addresses (of a list of users which is already created and stored in the system such as manager etc + to the email addresses for the users which are selected or specified in the multi person field of the particular record (form) )
The data in the Email1 and Email2 are fetched using separate queries in the spd itself from the Userdetails table(These variables contains the email addresses of the list of users (such as managers etc) I mentioned in the first part of the above requirement) The email validation and all are done in the front end level when the user record is first created in the system. Those email values (which is stored as string in sql) are concatenated to the emailAddr variable (using the concatenation (+) symbol as comma seperated).

Now again I need to fetch a separate set of email ids for a particular record (form) from the Userdetails table according to what is specified or selected for a multi person field in the form. For ex: if the multi person field has 3 persons specified or filled in them for a particular record/form , I need to fetch 3 email address for the 3 persons for that particular record and concatenate these email addresses to the emailAddr variable in the spd.
When the service runs , it will execute this spd and takes the values from the emailAddr field and according to a template that is defined in config file of the service it will send the notifications. The template defines the recipients, subject , body of the email and other details like the SQL connection.
This service is already running and sends notifications without issues. I just need to find a way to concatenate the multiple result set from the query as comma separated string to the variable 'emailAddr' so that the service can pick up the whole list of email address value strings.

Richard Deeming · Answer 2 · 2021-05-27T22:48:00

Solution 2

For SQL Server 2017 or later, use STRING_AGG:
STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs[^]

For older versions, there are numerous methods documented here:
Concatenating Row Values in Transact-SQL - Simple Talk[^]

Posted 27-May-21 22:48pm

Richard Deeming