How to return a comma separated list, in one column, from a field in multiple rows.

If you have, for example, email addresses on multiple rows in a table but want to return them as one column, \
comma separated.

Post Content

Database

The user defined function

The query that calls the function

Will Return something like this

Varchar

Database[-][--][++]

Example: Database table

ID | report_id | email
---|---------------
1  | 1         | email1@email.com
2  | 1         | email2@email.com
3  | 1         | email3@email.com

I want a return of

report_id | email
----------|------------
1         | email1@emailcom,email2@email.com,email3@email.com

To achieve this, you must create a user defined function which will concat the email field, then call \
that function from your SELECT statement

The user defined function[-][--][++]

CREATE FUNCTION dbo.fnConcatESPEmail (@reportID as int)  
RETURNS varchar(4000) AS  
BEGIN 
    DECLARE @email as varchar(4000)
    SET @email = ''
    SELECT @email = @email + email + ','
    FROM tba_ReportOptions_Email
    Where tba_ReportOptions_Email.report_id = @reportID
    IF len(@email) > 0 SET @email = substring(@email, 0, len(@email)) --remove last comma
    RETURN @email

END

The query that calls the function[-][--][++]

SELECT
  name as ReportName,
  dbo.fnConcatESPEmail (report_id) as email
FROM
  esp.dbo.tba_Reports r
WHERE
  package_id = 2
  AND disabled = 0

Will Return something like this[-][--][++]

Name 1       emailx@emailx.com
ReportName   emaily@email.com,emailyz@email.com,email@email.com
Name3        email@email.com,email2@email.com

Varchar[-][--][++]

Note: it is important to use varchar instead of nvarchar. See the query below, if I have this \
function on server1, but pass serverNum=2 it will show the email, but only the last one, it seems \
the concat + does not work right, it was a very strange occurrence, but changeing the return to varchar instead of \
nvarchar worked.

CREATE FUNCTION dbo.fnConcatESPEmail (@reportID as int, @serverNum as int)
RETURNS varchar(4000) AS  
BEGIN 
    DECLARE @email as varchar(4000)
    SET @email = ''

    IF (@serverNum = 1) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END ELSE IF (@serverNum = 2) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql2].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END ELSE IF (@serverNum = 3) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql3].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END ELSE IF (@serverNum = 4) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql4].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END ELSE IF (@serverNum = 5) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql5].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END ELSE IF (@serverNum = 6) BEGIN
        SELECT @email = @email + email + ','
        FROM [dyna-sql6].esp.dbo.tba_ReportOptions_Email
        Where report_id = @reportID
    END

    IF len(@email) > 0 SET @email = substring(@email, 0, len(@email)) --remove last comma
    RETURN @email
END

Then I call the above, multi server function with this large multi-server unioned query

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        1 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 1) as email
FROM
    [dyna-sql].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

UNION ALL

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql2].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql2].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        2 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 2) as email
FROM
    [dyna-sql2].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql2].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

UNION ALL

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql3].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql3].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        3 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 3) as email
FROM
    [dyna-sql3].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql3].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

UNION ALL

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql4].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql4].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        4 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 4) as email
FROM
    [dyna-sql4].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql4].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

UNION ALL

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql5].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql5].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        5 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 5) as email
FROM
    [dyna-sql5].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql5].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

UNION ALL

SELECT
        App_Extract_Job_Number as Extract,
        [App_name],
        report_id,
        name as ReportName,
        (SELECT  top 1 app_host_name FROM [dyna-sql6].ebis_prod.dbo.tblApplications WHERE APP_Company_APP_ID = (
          SELECT APP_Company_APP_ID FROM [dyna-sql6].ebis_prod.dbo.tblApplications WHERE APP_ID = EBIS_dlr_app_id
        ) AND APP_Host_Name IS NOT NULL) as Hostname,
        6 as server,
        EBIS_dlr_app_id,
        EBIS_comp_app_id,
        esp.dbo.fnConcatESPEmail (report_id, 6) as email
FROM
    [dyna-sql6].esp.dbo.tba_Reports r
    INNER JOIN [dyna-sql6].ebis_prod.dbo.tblApplications ta on ta.app_id = r.EBIS_dlr_app_id
WHERE
    package_id = 2
    AND disabled = 0

ORDER BY APP_NAME, ReportName ASC

Concat Rows in MSSQL