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.
ID | report_id | email
---|---------------
1 | 1 | email1@email.com
2 | 1 | email2@email.com
3 | 1 | email3@email.com
report_id | email
----------|------------
1 | email1@emailcom,email2@email.com,email3@email.com
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
SELECT
name as ReportName,
dbo.fnConcatESPEmail (report_id) as email
FROM
esp.dbo.tba_Reports r
WHERE
package_id = 2
AND disabled = 0
Name 1 emailx@emailx.com
ReportName emaily@email.com,emailyz@email.com,email@email.com
Name3 email@email.com,email2@email.com
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
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