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)?

Why you're storing comma delimited strings in the first place? The real problem here is the design. — Ilyes, Oct 01 '19 at 17:29
Is there a limit on how many codes can be in the codes column? — avery_larry, Oct 01 '19 at 17:36
[`STRING_SPLIT`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2016) — Thom A, Oct 01 '19 at 17:46
@Sami I am just a consumer of this data. Didn't design it. Nor can I change it. — AngryHacker, Oct 01 '19 at 17:59
@forpas Actually yes, I assumed the box was 2016, but it's 2017. I'll fix the tags. — AngryHacker, Oct 01 '19 at 18:07

score 2 · Answer 1 · answered Oct 01 '19 at 17:53

Since you are on 2016, one option would be string_split, but there is not gtd of maintaining the sequence. Perhaps this will help

Example

Select A.ID
      ,Codes = B.S
 From  Main A
 Cross Apply (
                Select S = stuff((Select ', ' +[description] 
                                   From (
                                          Select RetSeq = Row_Number() over (Order By (Select null))
                                                ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(100)')))
                                           From  (Select x = Cast('<x>' + replace((Select replace(A.[codes],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                                           Cross Apply x.nodes('x') AS B(i)
                                         ) B1
                                    Join Lookup B2 on B1.RetVal=B2.code
                                    Order by RetSeq
                                    For XML Path (''))
                                  ,1,2,'') 
             ) B

Returns

ID  Codes
1   High Rise, Mobile Home
2   Proposed Attached

Note Items missing because Lookup was not complete.

score 2 · Accepted Answer · answered Oct 01 '19 at 18:07

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

score 1 · Answer 3 · answered Oct 01 '19 at 17:35

1

For the main table you are going to want to do a cross apply STRING_SPLIT then you can join it to your lookup table and use a stuff() with for xml

You can learn about the cross apply STRING_SPLIT (SS 2016) here: Turning a Comma Separated string into individual rows (other methods are listed)

You can learn on the stuff with for xml here: Convert multiple rows into one with comma as separator

answered Oct 01 '19 at 17:35

CaveTroll

21
3

That function won't guarantee the order of rows and presumably they should correspond with the codes. – shawnt00 Oct 01 '19 at 17:48

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

3 Answers3