I’m not sure how deep you’re into SQL, but you’ve obviously already discovered the tricky part: If there is more than one result, your query will return more than one line – and that doesn’t fit into one single cell. However, SQL has some nice functions to merge such results. As a first step, you should transfer the logic into something like a Stored Procedure, Function, or View. I’ll show you the View option:
CREATE VIEW [dbo].[Custom_View_CaseSoftLocks] AS SELECT case_pk ,users = STUFF(( SELECT ', ' + sl.user_id FROM Pob_Soft_Lock sl with(nolock) WHERE sl.root_pk = cas.case_pk FOR XML PATH('') ), 1, 1, '') FROM Case_Table cas
Hint: Test the Select part with “where cas.id = ‘<any case ID>'” in your SQL Management Studio before you proceed.
And now you can query that in your column definition:
(select top 1 sl.users from dbo.Custom_View_CaseSoftLocks sl where sl.case_pk=case_pk)
The “top 1” is not necessary, but I usually take it as an additional security to make really sure to have only one result.