Need to create join on multiple ids on same cell

Question

I have a table uploadedFiles which I have a column assign_users_id which has multiple users in a single cell:

+---------+-------------+----------------+
| File_id | file_Name   | assign_users_id|
+---------+-------------+----------------+
| 1       |     file1   | 101,102        |
| 2       |     file2   | 99,101         |
| 3       |     file3   | 100            |
| 4       |     file4   | 80,90,100      |
+---------+-------------+----------------+

My user table like this:

    +---------+-----------+
    | user_id | user_Name |
    +---------+-----------+
    | 101     |    Farhat | 
    | 99      |    Rizwan | 
    | 80      |    Sajid  | 
    | 90      |    Alex   | 
    +---------+-----------+

Now I need to create a join between user_id and assign_users_id to get the user name.

This is my required output:

    +----------+-----------------+-----------------+
    |file Name | user_Name       | assign_users_id |
    +----------+-----------------+-----------------+
    | file1    |  Farhat,Ali     | 101,102         |
    | file2    |  Ale,Farhat     | 99,101          |
    | file3    |  Farh           | 100             |
    | file4    |  Ali,usman,Farh | 80,90,100       |
    +----------+-----------------+-----------------+

Answer 1

WARNING, this violates some basic database design principles. Like @a_horse_with_no_name notes, you want separate values (user names, user IDs) in separate rows / columns, not aggregated in a single comma-separated field. IMHO this is the most important reason you're not succeeding in writing this query on your own; otherwise, some simple JOINs would suffice.

But, if you insist, it can be done. Here's an example, using the STRING_SPLIT function from SQL Server 2016 to split comma separated strings and this trick to join them again:

SELECT file_name, LEFT(ca.value, LEN(ca.value) - 1), assign_users_id
FROM files AS extern
CROSS APPLY (
  SELECT user_name + ','
    FROM files AS intern
    CROSS APPLY STRING_SPLIT(assign_users_id, ',')
    INNER JOIN users
      ON users.user_id = value
    WHERE intern.file_name = extern.file_name
    FOR XML PATH('')  
) ca(value)
GROUP BY file_name, ca.value, assign_users_id

SQLFiddle