I working on a SQL query to obtain the common terms in strings in multiple rows and merge it with '/', For example

ID ROW1  ROW2  FINAL_RESULT
1   A     A,B      A/B
2   A/B   A,C      A/B,C
3   A,B   A,C      A/B,C

I'm working on with the below query, but it didn't give me the result what I'm expecting

update table1
set  FINAL_RESULT = 
 case when ( CHARINDEX( ROW1  ,ROW2 ) > 0 ) then
                    ROW1    '/'  Replace(ROW2  , ROW1  , '')

CodePudding user response：

This method uses a full join of string_splits.
To find what's common or uncommon between the columns.
Then glues them together.

update t
set final_result = CONCAT(ca.common, '/', ca.uncommon)
from table1 t
outer apply (
  select 
   string_agg(common,',') as common, 
   string_agg(uncommon,',') as uncommon
  from (
    select 
      case when s1.value is null then s2.value
           when s2.value is null then s1.value 
      end as uncommon
    , case when s1.value = s2.value then s1.value 
      end as common
    from      string_split(replace(col1,'/',','),',') s1
    full join string_split(replace(col2,'/',','),',') s2
      on s1.value = s2.value
  ) q
) ca;

select *
from table1

db<>fiddle here

Or add a UDF for the purpose.

create function [dbo].[CombineCommonTerms]
(
    @Value1 varchar(100),
    @Value2 varchar(100), 
    @Delim char(1) = '/'
)
returns varchar(200)
as 
begin
    declare @tbl1 table (col varchar(30));
    declare @tbl2 table (col varchar(30));
    
    set @Value1 = replace(@Value1,@Delim,',');
    declare @Xml1 xml = cast('<d>'  replace(@Value1,',','</d><d>')  '</d>' as xml);
    
    set @Value2 = replace(@Value2,@Delim,',');
    declare @Xml2 xml = cast('<d>'  replace(@Value2,',','</d><d>')  '</d>' as xml);
    
    insert into @tbl1 (col)
    select  distinct ltrim(rtrim(a.split.value('.', 'varchar(30)') )) as col
    from    @Xml1.nodes('/d') a(split);
    
    insert into @tbl2 (col)
    select distinct ltrim(rtrim(a.split.value('.', 'varchar(30)'))) as col
    from    @Xml2.nodes('/d') a(split);
    
    declare @Common varchar(100);
    select @Common = concat(@Common ',', t1.col)
    from @tbl1 t1 
    inner join @tbl2 t2 
      on t2.col = t1.col;
    
    declare @Uncommon varchar(100);
    select @Uncommon = concat(@Uncommon ',', coalesce(t1.col, t2.col))
    from @tbl1 t1 
    full join @tbl2 t2 
      on t2.col = t1.col
    where (t1.col is null or t2.col is null);
    
    return concat(@Common, @Delim   @Uncommon);
end;

update t
set final_result = [dbo].[CombineCommonTerms](col1, col2, default)
from table1 t;

select *
from table1;

db<>fiddle here