SQL Server - Return multiple row values in single row

Question

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   Conrad@test.com

Output I need is the e-mail addresses in a single row / one column:

conrad@test.com; ali@test.com; will@test.com

Can someone please help.

[**This**](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) might be helpful — Mahmoud Gamal, Aug 29 '12 at 07:27

codingbiz · Accepted Answer · 2012-08-29T07:38:54.013

3

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

edited Aug 29 '12 at 07:38

answered Aug 29 '12 at 07:27

codingbiz

26,179
8
59
96

score 1 · Answer 2 · edited Mar 26 '14 at 04:24

FOR XML PATH() is good solution for SQL Server:

WITH x AS (
    SELECT 'x@example.com' AS mail
    UNION
    SELECT 'y@example.com'
    UNION
    SELECT 'z@example.com'
)
SELECT
    STUFF((SELECT
        mail + ','
    FROM
        x
    FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'), 1, 1, '')

Query above returns @example.com,y@example.com,z@example.com,.

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

score 1 · Answer 3 · edited Mar 26 '14 at 04:23

1

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,y@example.com,z@example.com,

edited Mar 26 '14 at 04:23

jdphenix

15,022
3
41
74

answered Aug 29 '12 at 07:36

Pankaj Agarwal

11,191
12
43
59

score 0 · Answer 4 · answered Aug 29 '12 at 07:32

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

SQL Server - Return multiple row values in single row

4 Answers4