SQL query that concatenates values from duplicate rows in a single table
Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG however that provides equivalent functionality.

You have some manual options though:


build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee
use xml functions
use an already built aggregate (similar as first option)


A solution for your current case (just took Brad's answer from SO and customized for your table) would be:

SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
    SELECT role + ','
    FROM employee AS intern
    WHERE extern.name = intern.name
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;


These details are gathered from different sources:


Simulating group_concat MySQL function in MS SQL Server 2005? - on StackOverflow.com
Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 - blog
GROUP_CONCAT in SQL Server - blog
T-SQL Equivalent For Group_Concat() Function - MSDN