Put all the names together

The 'hardest' part for this one was to construct a string with all the names in it. One way to do that is with a cursor. Cursors are great but you have to be careful since they can take up some memory and slow down the performance.

So to avoid that, I used this simple trick:

declare @Names VARCHAR(2000)
select @Names = isnull(@Names + '; ', '') + o.Name
from Files f
inner join Organizations o on f.OrganizationID = o.OrganizationID
where ProjectID = @ProjectID
group by o.Name

print @Names

This select loops through the results. For the first row the @Names variable is null so the isnull handles that no semi-colon is added. Just the name. For the other rows, @Names is no longer null so a semi-colon is added to it to separate between names.

To check the result I printed out the variable and it looked just as I expected.

Create a Function

So since this worked, I decided to create a function with it, so I can use it in a view on the Projects table.

create or alter function dbo.fnGetNamesByProjectID
(
@ProjectID uniqueidentifier
)
returns nvarchar(2000)
as
begin
  declare @Names VARCHAR(2000)
  select @Names = isnull(@Names + '; ', '') + o.Name
  from Files f
  inner join Organizations o on f.OrganizationID = o.OrganizationID
  where ProjectID = @ProjectID
  group by o.Name

  return @Names
end
GO

Use this in a view

With that Function in place, I could use it in a view:

create or alter view vwProjects as
select p.ProjectID, p.Code, p.Description,
dbo.fnGetNamesByProjectID(p.ProjectID) as Organizations
from Projects p

And with that one, I could easily perform the search from the application as requested:

select *
from vwProjects
where Organizations like '%Exact%'

One thing to consider

Using a function in a view works well, but do note that the field is not indexed. For really large table this could be a performance killer, so that is one thing to consider.

Another thing to consider has also got to do with performance. Pre SQL 2019 the execution plan (which is very imporant for the performance of your query) was not well prepared for functions. So on older versions of SQL Server you should really test this one before doing a lot of work on something that will not perform as expected.

This problem is however solved in SQL 2019. So if upgrading the SQL Server to that version is possible, it is really worth the work.

An alternative without a Function

Another way to do this, is using XML and Stuff. Yes, you can output the results of any select query to the XML format. Try this one for example:

select name
from sysobjects
where xType = 'U'
order by name
for xml path ('')

When you run this one, you will see the XML file as the result. Click on it to see the content of the generated XML file.

With that XML File you can use the Stuff Function to put the values together.

select p.Code,
stuff((select distinct ',' + o.Name
  from Files f
  inner join Organizations o
    on f.OrganizationID = o.OrganizationID
  where f.ProjectID = p.ProjectID
  for xml path ('')), 1, 1, '') as Organizations
from Projects p

As you can see, the highlighted Stuff Function requires 4 Parameters.

The list of values
In this case the XML that is generated by the query.
The Character to be removed per value
I haven't found a use for this one yet. But if you specify 4 in this case, the 4th character of every name would be removed.
First Characters of the end result to be replaced
This one makes more sense. Each name would have a leading comma. When they are all put together, the first comma can go.
The Replace for Parameter 3
In this case I wanted to remove the leading comma. So I replaced it by an empty string.

So in most cases the Stuff Function will be used with 1, 1, ''. But if you want to do it defferently, now you know what those Parameters stand for.

An alternative without Stuff

There is an even easier Function that you could use. That is, if you are running SQL Server 2017 or newer. This one is calles string_agg().

It can be used like any other aggregate Function like min(), max() of sum(). There are 2 arguments needed. The first one is the Field you want to use for the concatenation. The second is the separator you want to use.

Here is an example:

select oms25_0 as Department,
(
  select string_agg(h.FullName, ', ')
  from humres h
  where h.costcenter = cc.kstplcode
)
from kstpl cc

As you can see its syntax is pretty simple. Only thing you should know is that the Order By is not where you likely expect it to be.

Here is an example with the names sorted:

select cmp_name as Company,
(
  select string_agg(cp.FullName, ', ')
  within group (order by cp.FullName)
  from cicntp cp
  where cp.cmp_wwn = c.cmp_wwn
) as ContactPersons
from cicmpy c

To Function, To Stuff or String_Agg?

That is the question. Stuff is a specific Function and I don't use it that much, to be honest. So I always have to check for the exact syntax for it. But in this case, it would have done a great job.

String_agg() would even be better, if not the customer was running on an older SQL Server version. So be sure to check for that before you come up with the solution.

One advantage that a Function has is that you can use it in multiple places and maintain in just one single place. Sure, if you would need it somewhere else, you could just copy and paste the stuff there in as well. But I have been on a project where a Developer did this and it needed to be modified. I had to go through quite a lot of Views, Functions and Stored Procedures to modify each and everyone of them.

Based on such experiences I prefer to keep things in one place and use it from there. If performance allows it, of course!

That's it for today

Two ways of putting strings together from a result set. Depending on how much it will be re-used in other Views, Functions or Stored Procedures, you can decide which one works best for your scenario.

If you do use it in a project, do let me know which one you prefered!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!