In order to concatenate strings (delimited by with a string) from multiple rows in a SQL Table to a single field the Coalesce command is the one to use. Typically COALESCE is used to return a single field value which represents multiple rows concatenated by a string.
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList
The output from the following would be something like 1,2, 3
The following example uses a function to return a joined table field with the primary data selected. The challenge was to return a single row from one table while returning values from the joined table into a single field. To accomplish this I used syntax similar to the following:
i.e.
SELECT personID,dbo.fn_CombineValues(personID) Roles
FROM [People]
The following function was created to support the above sql query. The function accepts the key to be used in the the joined table and returns a string value representing in this case the roles related to the primary table.
CREATE FUNCTION fn_CombineValues
(
@FK_ID INT --The foreign key from TableA which is used to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);
SELECT @SomeColumnList = COALESCE(@SomeColumnList + ', ', '') + CAST(Role AS varchar(20))
FROM dbo.SA_PeopleRoles C
INNER JOIN dbo.SA_Roles r ON r.roleid=c.RoleID
WHERE C.personID = @FK_ID;
RETURN
(
SELECT @SomeColumnList
)
END
References:http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value