Display The the result with comma seperated Using join

Question

There are two tables in my Database

1. project - project_id (Nvarchar) , client_id (Nvarchar)
2. client - id (int), name (Nvarchar)

Dependency- project.project_id=client .id

I want to create random column that display the client_name(comma seperated) which is stored in Client table where client_id from project is depends on the id of Client table.

Here is a table and expected results-

Answer 1

Try this Code

SELECT a.project_id, a.client_id, 
                        (
                            select ', '+co.name
                            from Client as co
                            where ','+a.client_id+',' like '%,'+CONVERT(NVARCHAR,co.id)+',%'
                            for xml path(''), type
                        ).value('substring(text()[1], 2)', 'varchar(max)') as client_name 
                    from project as a