 <p>Since you also have the <code>sql-server</code> tag on your question, unfortunately sql-server doesn't have <code>GROUP_CONCAT</code>, an alternative like <a href="http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005">FOR XML / STUFF</a> is needed:</p> <pre><code>SELECT f.id, f.movie_name, STUFF((SELECT ',' + p.person_name FROM persons p INNER JOIN films_cast fc on fc.person_id = p.id WHERE fc.film_id = f.id AND fc.type = 'director' FOR XML PATH ('')) , 1, 1, '') AS director, STUFF((SELECT ',' + p.person_name FROM persons p INNER JOIN films_cast fc on fc.person_id = p.id WHERE fc.film_id = f.id AND fc.type = 'writer' FOR XML PATH ('')) , 1, 1, '') AS writer, STUFF((SELECT ',' + p.person_name FROM persons p INNER JOIN films_cast fc on fc.person_id = p.id WHERE fc.film_id = f.id AND fc.type = 'actor' FOR XML PATH ('')) , 1, 1, '') AS cast FROM films f; </code></pre> <p><a href="http://sqlfiddle.com/#!3/e1cd1/1" rel="nofollow">SQLFiddle here</a></p>