sql server - Joining a table based on comma separated values -

sql server - Joining a table based on comma separated values -

how can bring together 2 tables, 1 of tables has multiple comma separated values in 1 column reference id in column?

1st table

name | course of study id ==================== zishan | 1,2,3 ellen | 2,3,4

2nd table

course id | course of study name ======================= 1 | java 2 | c++ 3 | oracle 4 | dot net

maybe uglyness, have not checked results:

select names.name, courses.course_name names inner bring together courses on ',' + names.course_ids + ',' '%,' + cast(courses.course_id nvarchar(20)) + ',%'

sql-server