Fetch fields like email address from multiple records into single cell
Very often we might get a situation, where we have to fetch a field from multiple records into single cell value. Particularly, things like email ID list, since it can be copied over to the email applications (ex: outlook) / tools. COALESCE is a very good in-built function, which helps to achieve what we need. It simply concatenating the multiple rows value into one cell.
Here is the sample script to try. For your real world use case, you can simply replace the table & field name from the 2nd last SQL statement, where COALESCE is used to do the job.
IF OBJECT_ID('TempDB..#Users','U') IS NOT NULL
BEGIN
Drop table TempDB..#Users
END
--Creating user table
Create table #Users (sLogin Varchar(15), sName varchar(25), dCreateDate DateTime, dLastLoginDate DateTime, sEmailID varchar(50))
--Inserting sample data to the Users table
Insert into #Users (sLogin, sName, dCreateDate, dLastLoginDate)
Select 'AKevin' sLogin, 'Kevin A' sName, '2025-04-06' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'JMike' sLogin, 'Mike J' sName, '2025-04-11' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'MPal' sLogin, 'Pal M' sName, '2025-05-21' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'CGautam' sLogin, 'Gautam C' sName, '2025-04-14' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'MHenry' sLogin, 'Henry M' sName, '2025-06-06' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'MChris' sLogin, 'Chris M' sName, '2025-05-28' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'SJohn' sLogin, 'John S' sName, '2025-03-15' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'KRudy' sLogin, 'Rudy K' sName, '2025-06-09' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'AThomos' sLogin, 'Thomas A' sName, '2025-05-23' dCreateDate, GetDate() dLastLoginDate
UNION
Select 'LKatie' sLogin, 'Katie L' sName, '2025-06-20' dCreateDate, GetDate() dLastLoginDate
--The email ID will be UserID+domain name. So, simply we can update using one sql
UPDATE #Users
SET sEmailID = sLogin + '@mydomain.com'
-- Simply selecting the data to see how it looks
SELECT * FROM #Users
--SQL to get the email address in one cell, which can be copied over to the email application.
DECLARE @EmailList NVarchar (max)
SELECT @EmailList = COALESCE(@EmailList + ';', '') + RTRIM(sEmailID) FROM #Users
SELECT @EmailList As [Email ID list] 
