How to resolve comma separated values in a set based manner?

Question

I have a main table, which contains comma separated codes for each id:

create table main (id int, codes nvarchar(3))

id  codes
1   HIR, RES, NAS
2   TA1, WQ9, PLM

And a lookup table which describes what those codes mean:

create table lookup (code nvarchar(3), description nvarchar(100))

code  description
HIR   High Rise
NAS   Mobile Home
PLM   Proposed Attached
...

I want to select from the main table and replace the comma separated list of codes with a comma separated lists of corresponding descriptions:

id  codes
1   High Rise, Residential, Mobile Home

I figured out how to loop through each row, break apart the CSV, query each manually, build the string back and produce what I want.

However, is there a way to do it in a set based manner (and faster)?

Answer 1

Since you can use SQL Server 2017 there is STRING_AGG():

select m.id, string_agg(l.description, ', ') within group (order by charindex(l.code, m.codes)) codes
from main m inner join lookup l
on concat(',', replace(m.codes, ', ', ','), ',') like concat('%', l.code, '%')
group by m.id

See the demo.
Results:

> id | codes                              
> -: | :----------------------------------
>  1 | High Rise, Residential, Mobile Home