SQL COALESCE Command Features

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
: 8pt; overflow: visible; border-style: none; padding: 0px;"> 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

Author

Admin

comments powered by Disqus

Categories

Recent Tweets

Retweeted by @dyardy Hey C# peeps, we’re trying C# in browser with reference content. Try it here: docs.microsoft.com/dotnet/csharp/… reply with feedback /cc @LadyNaggaga
23 Amazing Vintage Photographs Taken Inside WWII Tank Factories ~ vintage everyday vintag.es/2017/10/23-ama…
When is AI NOT Search? Artificiality intelligence==Search Engine (there cases when this is not true)
@projectedxyz Large data vs big data? Industry has so misused buzz words to sell products, and so you end up nothi… twitter.com/i/web/status/9…