How to parse comma delimited list and join those values on another table and reconcatenate? [closed]

I’m looking for a way to parse a semicolon delimited list of values, join on another table using the values and return values from that other table and re-concatenate into a new field.

Here are the examples:

Table A:

Here is the table with the PFD values:

Table B:

Here is the results I’d like:

Table C:

Is this possible? Thank you!

Answer

Assuming you are using at least SQL Server 2016, you can use a combination of string_split and for xml path as follows:

select *
from tableA
cross apply (
    select stuff((
        select concat(';', b.alias)
        from String_Split(device_platform_id,';')
        join tableB b on b.name=value
        for xml path ('')
    ),1,1, '') Device_Platform_Alias
)d

Example DB<>Fiddle

If you happen to be using a version prior to 2016 you can use this alternative method that uses xml to split the value on ‘;’ instead of the string_split function:

select *
from tableA
cross apply (
    select stuff((
        select concat(';', b.alias)
        from (
          select value = y.i.value('(./text())[1]', 'varchar(max)')
          from ( 
            select x = convert(xml, '<i>' + replace(device_platform_id, ';', '</i><i>') + '</i>').query('.')
          ) as a cross apply x.nodes('i') as y(i)
        )v
      join tableB b on b.name=value
      for xml path ('')
    ),1,1, '') Device_Platform_Alias
)d

Example DB<>Fiddle