0

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!

enter image description here

Here is a fiddle with table definitions and sample data:

https://dbfiddle.uk/fckBUuV-

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Saraf
  • 200
  • 10
  • 3
    You need to _fix the schema_ (because this design really is **broken**) to use a separate to store the hobbies for each person, where there is a separate row for each hobby for each person. Also, add a better primary key for the people table. – Joel Coehoorn Jul 06 '23 at 14:01
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Jul 06 '23 at 14:02
  • 2
    You should normalize it out into a separate link table `PersonHobby (PersonName, HobbyId)` then you can just do a simple join. – Charlieface Jul 06 '23 at 14:05
  • 1
    I would also suggest that storing Age for a person is problematic. The data is stale as soon as you save it. It would be FAR better to store their birthdate and calculate age on the fly. – Sean Lange Jul 06 '23 at 20:55

2 Answers2

2

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
Sadek Mehri
  • 50
  • 1
  • 5
  • 1
    thank you very much for your reply, I really appreciate it. This is as accepted answer as the one picked. I just really lacked a short brief how it works and Joel had written and explained in details what exactly the solution does and how it works. – Saraf Jul 09 '23 at 06:43
  • 1
    The query selects the name, age, and hobbies columns from the people table. The hobbies column is calculated using a subquery. In the subquery, the hobbies associated with each person are retrieved by splitting the values in the hobbies column using commas. The resulting values are then used to filter the rows in the hobbies table based on matching identifiers. Finally, the filtered hobbies are aggregated into a single string using the STRING_AGG function. – Sadek Mehri Jul 09 '23 at 09:55
2

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794