sql server - SQL Query using inner join -

categorytable

   code   name     1      food     2      non-food 

existing table consists list of category, example, have 2 food , non-food

as challenge, assigning tenants category or categories (multiple assignment, there tenants categorized food , non-food). i used insert tenant , code new table creating output

tenantassigntable

tenant    code    tenant1   1,2     tenant2   1    

what need do, load tenantassingtable gridview consisting name of categorycode this

desired output

   tenant    ccode    name     tenant1   1,2    food,non-food     tenant2   1      food 

i used inner join in code, limited have string of combined code in code column.

select a.tenant, a.ccode, b.name tenantassigntable inner join categorytable b on a.ccode = b.code 

is there anyway achieve kind of output? know unusual in sql coding challenge desired output concerned , needs have multiple assignment of category single tenant.

thanks in advance!

think simple;

you can like , xml path

declare @categorytable table (code varchar(50), name varchar(50)) insert @categorytable values   ('1', 'food'), ('2', 'non-food')  declare @tenantassigntable table (tenant varchar(50), code varchar(50)) insert @tenantassigntable values   ('tenant1', '1,2'), ('tenant2', '1')  select     t.tenant ,     t.code,     stuff(         (select             ',' + c.name                     @categorytable c                     ',' + replace(t.code, ' ', '') + ',' '%,' + c.code + ',%'         xml path('')     ), 1, 1, '')     @tenantassigntable t 

result:

tenant          code         --------------- ------------ --------------- tenant1         1,2          food,non-food tenant2         1            food