0

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       |
    +----------+-----------------+-----------------+
3

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

enter image description here

SQLFiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.