CSV of IDs to CSV of Values

csv, sql, sql-server

Solution

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

Problem

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!

Original source