Return string of description given string of IDs (separated by commas)

Question

Hi SQL experts,

I have one table A that has a column C and a lookup table (lookup) that provides a description given an ID. Here the setup:

table A with column C values:

1,2,3
2,3,4

table lookup:
1, 'This'
2, 'is'
3, 'tricky'
4, 'SQL'

Provide a SQL (SQL Server 2005) statement that returns the following strings:

Input: 1,2,3    Output: 'This','Is','tricky'
Input: 2,3,4    Output: 'Is','tricky','SQL'

basically turning the string of IDs (from an input table A) into a string of descriptions

Answer 1

The Samples that come with SQL Server 2005 include a CLR function called Split(). It's the best way of splitting comma-separated lists like this by far.

Suppose you have a table called inputs, with a column called input.

I forget what the particular outputs of dbo.Split() are... so work with me here. Let's call the fields id and val, where id tells us which entry it is in the list.

WITH
separated AS (
   SELECT i.input, s.id, s.val
   FROM 
       dbo.inputs AS i 
       CROSS APPLY 
       dbo.Split(i.input) AS s
)
, converted AS (
    SELECT s.input, s.id, m.string
    FROM 
        separated AS s
        JOIN
        dbo.mapping AS m
            ON m.number = CAST(s.val AS varchar(5))
)
SELECT c.input, (SELECT string + ' ' FROM converted AS c2 WHERE c2.input = c.input ORDER BY id FOR XML PATH('')) AS converted_string
FROM converted AS c
GROUP BY c.input;