IF OBJECT_ID('tempDb..#employee') IS NOT NULL DROP TABLE #employee
IF OBJECT_ID('tempDb..#roles') IS NOT NULL DROP TABLE #roles
IF OBJECT_ID('tempDb..#employeeRoles') IS NOT NULL DROP TABLE #employeeRoles

CREATE TABLE #employee (
	[employeeId] INT PRIMARY KEY
	, [name] VARCHAR(255)
	)

CREATE TABLE #roles (
	[roleId] INT PRIMARY KEY
	, [roleName] VARCHAR(255)
	)

CREATE TABLE #employeeRoles (
	[employeeID] INT -- FOREIGN KEY 
	, [roleID] INT -- FOREIGN KEY

	PRIMARY KEY([employeeID], [roleID])
	)

/* Data */
INSERT #employee ([employeeID], [name])
      SELECT 1, 'Fred Flintstone'
UNION SELECT 2, 'Barney Rubble'

INSERT #roles ([roleID], [roleName])
      SELECT 1, 'Saftey Inspector'
UNION SELECT 2, 'Handy Man'
UNION SELECT 3, 'Plumber'

INSERT #employeeRoles ([employeeID], [roleID])
      SELECT 1, 1 -- Fred (Saftey)
UNION SELECT 1, 3 -- Fred (Plumber)
UNION SELECT 2, 2 -- Barney (Handy Man)


/* FOR XML SUB_SELECT */
SELECT
	[employee]
	, LEFT([roles], LEN([roles]) -1) AS [Roles]
FROM
	(
	SELECT
		e.[name] AS [employee]
		, [Roles] = (
			SELECT [rolename] + ', '
			FROM
				#employeeRoles er
				JOIN #roles r ON r.[roleID] = er.[roleID]
			WHERE
				er.[employeeID] = e.[employeeID]
			FOR XML PATH('')
			)
	FROM
		#employee e
	)
	rep

/* Attempt at OUTER APPLY */
SELECT
	e.[name]
	, rl.[roles]
FROM
	#employee e

	OUTER APPLY (
		SELECT [rolename] + ', ' AS [ROLES]
		FROM
			#employeeRoles er
			JOIN #roles r ON r.[roleID] = er.[roleID]
		WHERE
			er.[employeeID] = e.[employeeID]
		FOR XML PATH('')
		)
		rl

SELECT
	e.[name]
	, LEFT(rl.col,LEN(rl.col)-1)
FROM
	#employee e

	OUTER APPLY (
		SELECT [rolename] + ', ' AS [ROLES]
		FROM
			#employeeRoles er
			JOIN #roles r ON r.[roleID] = er.[roleID]
		WHERE
			er.[employeeID] = e.[employeeID]
		FOR XML PATH('')
		)
		rl(col)