Finding the Differences Between 2 Columns, clarify

Question

As stated in this question Let's say I have data that looks like this:

Declare @YourTable table (ColA varchar(150),ColB varchar(150))
Insert Into @YourTable values
('John, Sally','John, Sally, Cindy, Steve')

Select A.*
      ,B.*
 From  @YourTable A
 Outer Apply (
               Select Diff=value
                From (
                       Select value=ltrim(rtrim(value)) From string_split(ColA,',')
                       Union All
                       Select value=ltrim(rtrim(value)) From string_split(ColB,',')
                     ) B1
                Group By Value
                Having count(*)=1
             ) B

you got

ColA                ColB                        Diff
John, Sally         John, Sally, Cindy, Steve   Cindy
John, Sally         John, Sally, Cindy, Steve   Steve

but how to get

ColA                ColB                        Diff
John, Sally         John, Sally, Cindy, Steve   Cindy, Steve

Answer 1

One method is string_agg(), which I would put into the apply subquery:

Select A.*, B.*
 From  @YourTable A Outer Apply
       (select string_agg(diff, ', ') as diff
        from (Select Diff=value
              From (Select value=ltrim(rtrim(value)) From string_split(ColA,',')
                    Union All
                    Select value=ltrim(rtrim(value)) From string_split(ColB,',')
                   ) B1
              Group By Value
              Having count(*)=1
             ) B
       ) B;

But I think using a full join is simpler:

select A.*, B.*
from @YourTable A outer apply
     (select string_agg(coalesce(trim(a.value), trim(b.value)), ', ') as diff
      from string_split(ColA, ',') a full join
           string_split(ColB, ',') b
           on trim(a.value) = trim(b.value)
      where a.value is null or b.value is null
     ) b;

Here is a db<>fiddle.

Answer 2

You can select from a query and aggregate the result:

select cola, colb, string_agg(diff, ', ') as diffs
from ( <your query> ) q
group by cola, colb;