I have got this table which has email address of 3 employees in SQL Server
Table structure is
EmployeeId, EmployeeName, Employee email address
Data is like this:
1 Conrad [email protected]
Output I need is the e-mail addresses in a single row / one column:
[email protected]; [email protected]; [email protected]
Can someone please help.
You can try this: SQL Fiddle
SELECT SUBSTRING(
(SELECT ';' + e.EmailAddress
FROM Employee e
FOR XML PATH('')),2,8000) AS CSV
From SQL Server 2005 upward. See more here
You can try with COALESCE
DECLARE @listStr VARCHAR(MAX)
select @listStr = COALESCE(@listStr+',','') + Employee_email_address from table1 where EmployeeId = @EmployeeId
select @listStr
Query above will return @example.com,[email protected],[email protected],
FOR XML PATH() is good solution for SQL Server:
WITH x AS (
SELECT '[email protected]' AS mail
UNION
SELECT '[email protected]'
UNION
SELECT '[email protected]'
)
SELECT
STUFF((SELECT
mail + ','
FROM
x
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'), 1, 1, '')
Query above returns @example.com,[email protected],[email protected],.
In your case query would look like (could contain errors):
SELECT
EmployeeId,
EmployeeName,
(SELECT
STUFF((SELECT
[e.email address] + ','
FROM
employees e
WHERE
e.EmployeeId = EmployeeId
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'), 1, 1, '')) AS mails
FROM
employees
You can use cursor for that.
DECLARE @email VARCHAR(256)
DECLARE @EmailCollection VARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT Email
FROM tableName
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @email
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EmailCollection = @EmailCollection + @email + ';'
FETCH NEXT FROM db_cursor INTO @email
END
CLOSE db_cursor
DEALLOCATE db_cursor
And at the end you can return @EmailCollection
