CSV of IDs to CSV of Values

Question

Say I have a column in a database that consists of a comma separated list of IDs (please don't ask why :( ), i.e. a column like this:

id  |  ids
----------
1   |  1,3,4
2   |  2
3   |  1,2,5

And a table the ids relate to:

id  |  thing
---------------
1   |  fish
2   |  elephant
3   |  monkey
4   |  mongoose
5   |  kiwi

How can I select a comma separated list of the things, based of an id in the first table? For instance, selecting 1 would give me, 'fish,monkey,mongoose', 3 would give me 'fish,elephant,kiwi' etc.?

Thanks!

Answer 1

Try this

SELECT ID, things = STUFF(
(
  SELECT ',' + t2.thing 
     FROM Table2 AS t2
    INNER JOIN Table1 AS ti
    ON ',' + ti.ids + ',' LIKE '%,' + CONVERT(VARCHAR(12), t2.id) + ',%'
    WHERE ti.ID = tout.ID
    FOR XML PATH, TYPE
).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM Table1 AS tout
ORDER BY ID

SQL FIDDLE DEMO