SQL Server - Replace a comma separated field with the appropriate data from another table

I would like to SELECT the people table but dynamically assign the appropriate hobbies.

Instead of people having hobbies like 'A' or 'B' and 'C', I want to SELECT another table and get the appropriate hobby string.

One hobby is easy:

SELECT name, age,
(SELECT hobby
FROM hobbies
WHERE people.hobbies = hobbies.identifier) as hobbies
FROM people

But when I try to think of how to get multiple hobbies for a comma separated field and replace each identifier I reach a dead end. I tried to utilize STRING_SPLIT, STRING_AGG with no luck.

Any thoughts and/or ideas are most welcome!

Here is a fiddle with table definitions and sample data:

> https://dbfiddle.uk/fckBUuV-

By reading this article maybe this snippet might help you. This query below works in SqlServer.

SELECT p.name, p.age, 
    (SELECT STRING_AGG(hobby, ',')
     FROM hobbies h
     WHERE h.identifier IN (SELECT value
                            FROM STRING_SPLIT(p.hobbies, ','))
    ) AS hobbies
FROM people p

Try this:

SELECT p.Name, p.Age, string_agg(h.Hobby, ',') As Hobbies
FROM people p
CROSS APPLY string_split(p.Hobbies, ',') ph
INNER JOIN hobbies h on h.identifier = ph.value
GROUP BY p.Name, p.Age

See it work here:

> https://dbfiddle.uk/T0aEQyJ4

This works by using an APPLY (lateral join) on the string_split() of the people.Hobbies column to get the individual records for each person/hobby you should have had in the first place. From there it's a simple JOIN to the hobbies table to get the hobby names, and then GROUP BY the person data so we can use string_agg() to get the comma-separated list you want.

Again: all of this is poor practice. Don't use comma-separated data in your database. Keep a separate record (usually in it's own table) for each item from such lists, and it's usually better to return this data unaggregated; let the client code or reporting tool roll it up again.