WITH CTE
AS
(
	SELECT ROW_NUMBER() OVER 
		(
			PARTITION BY u2.UserID 
			ORDER BY MIN(u2.RoleAssignedDate)
		) AS RowNum,
		u2.UserID, 
		u2.RoleName
	FROM @UserRole u2
	GROUP BY u2.UserID,
		u2.RoleName
)
SELECT DISTINCT u1.UserID,
	SUBSTRING
	(
		(
			SELECT ', ' + c.RoleName AS '*'
			FROM CTE c
			WHERE u1.UserID = c.UserID
			ORDER BY c.RowNum
			FOR XML PATH('')
		), 3, 8000
	) AS Roles
FROM @UserRole u1;

 
SELECT DISTINCT u1.UserID,
REPLACE	(
			(SELECT u2.RoleName + ',' AS 'data()'
			FROM @UserRole u2
			WHERE u2.UserID = u1.UserID
			GROUP BY RoleName
			ORDER BY MIN(RoleAssignedDate)
			FOR XML PATH('')
			) + '$', ',$', ''
		) AS Roles
FROM @UserRole u1

SET NOCOUNT ON

-- Prepare sample data
DECLARE	@UserRole TABLE
	(
		UserID INT NOT NULL,
		RoleName VARCHAR(100) NOT NULL,
		ProjectID INT NOT NULL,
		RoleAssignedDate DATETIME NOT NULL
	) 

-- Populate sample data
INSERT	@UserRole
	(
		UserID,
		RoleName,
		ProjectID,
		RoleAssignedDate
	)
VALUES	(1112, 'Technical Director',          2041, '1967-02-20 04:21:13.490'),
	(1357, 'Training',                    1614, '1961-09-14 16:18:59.990'),
	(1836, 'Technical Director',          1628, '1987-07-30 11:22:45.060'),
	( 715, 'Accounting',                  1487, '1995-01-08 11:46:17.670'),
	( 162, 'Approver',                    1548, '2001-07-02 11:34:14.260'),
	(1975, 'Technical Director',          1614, '1955-11-10 03:55:05.560'),
	(1112, 'System Administrator',         831, '1956-04-25 08:26:54.040'),
	( 162, 'Technical Director',           986, '1989-12-17 15:51:04.330'),
	( 715, 'Accounting',                   461, '1954-11-27 00:45:52.830'),
	(1357, 'Developer',                   2064, '2006-12-15 23:43:55.470'),
	(1867, 'Technical Director',          1416, '2003-04-11 16:50:01.070'),
	(1975, 'Developer',                   1548, '1967-05-11 17:01:26.840'),
	(1975, 'Accounting',                  1089, '1988-06-20 00:52:16.070'),
	( 162, 'Marketing',                   1443, '1995-06-10 14:29:23.290'),
	(1112, 'Accounting',                  2109, '1999-12-05 09:07:46.620'),
	( 162, 'Technical Director',          1089, '1975-09-25 12:13:12.590'),
	( 162, 'International Sales Manager', 1628, '1984-05-30 09:25:18.330'),
	(1836, 'Technical Customer',           420, '1993-05-28 00:49:31.090'),
	(1357, 'Technical Director',          2036, '1979-04-25 23:38:48.120'),
	(1836, 'Developer',                   1628, '2006-07-26 20:36:53.420')

-- Start timing
SET STATISTICS IO ON
SET STATISTICS TIME ON

-- Peso
PRINT '*** Peso ***********************************************************************'

SELECT		b.UserID,
		STUFF(f.Roles, 1, 1, '') AS Roles
FROM		(
			SELECT		UserID
			FROM		@UserRole
			GROUP BY	UserID
		) AS b
CROSS APPLY	(
			SELECT		TOP(100) PERCENT
					',' + w.RoleName
			FROM		@UserRole AS w
			WHERE		w.UserID = b.UserID
			GROUP BY	w.RoleName
			ORDER BY	MIN(RoleAssignedDate)
			FOR XML		PATH('')
		) AS f(Roles)
ORDER BY	b.UserID

-- Puja Shah
PRINT '*** Puja Shah ******************************************************************'

SELECT		b.UserID,
		LEFT(b.Roles, LEN(b.Roles) - CHARINDEX(',', REVERSE(b.Roles))) AS Roles
FROM		(
			SELECT DISTINCT	a.UserID,
					CAST(a.Roles.query('distinct-values(/Root/Roles)') AS VARCHAR(MAX)) AS Roles
			FROM		(
						SELECT	u1.UserID,
							CAST(
								(
									SELECT DISTINCT	u2.RoleName + ',' AS Roles,
											ROW_NUMBER() OVER(PARTITION BY u2.UserID ORDER BY u2.RoleAssignedDate) AS RID
									FROM		@UserRole AS u2
									WHERE		u2.UserID = u1.UserID
									ORDER BY	RID
									FOR XML		PATH(''),
											ROOT('Root')
								) AS XML
							) AS Roles
						FROM	@UserRole AS u1
					) AS a
		) AS b
ORDER BY	b.UserID

-- Kailash Mishra		<- Fails ordering
PRINT '*** Kailash Mishra *************************************************************'

SELECT DISTINCT	a.UserID,
		SUBSTRING(b.RoleName, 1, LEN(b.RoleName) -1)
FROM		@UserRole AS a
CROSS APPLY	(
			SELECT DISTINCT	b.RoleName + ','
			FROM		@UserRole AS b
			WHERE		b.UserID = a.UserID
			FOR XML		PATH('')
		) AS b(RoleName) 
ORDER BY	a.UserID

-- Mike C
PRINT '*** Mike C *********************************************************************'

;WITH cte
AS (
	SELECT		ROW_NUMBER() OVER (PARTITION BY u2.UserID ORDER BY MIN(u2.RoleAssignedDate)) AS RowNum,
			u2.UserID, 
			u2.RoleName
        FROM		@UserRole AS u2
        GROUP BY	u2.UserID,
			u2.RoleName
)
SELECT DISTINCT	u1.UserID,
		SUBSTRING(
				(
					SELECT		',' + c.RoleName AS '*'
					FROM		cte AS c
					WHERE		u1.UserID = c.UserID
					ORDER BY	c.RowNum
					FOR XML		PATH('')
				), 2, 8000
			) AS Roles
FROM		@UserRole AS u1
ORDER BY	u1.UserID

-- Nick Hanson
PRINT '*** Nick Hanson ****************************************************************'

SELECT DISTINCT	u1.UserID,
		REPLACE (
				(
					SELECT		u2.RoleName + ',' AS 'data()'
					FROM		@UserRole AS u2
					WHERE		u2.UserID = u1.UserID
					GROUP BY	u2.RoleName
					ORDER BY	MIN(u2.RoleAssignedDate)
					FOR XML		PATH('')
				) + '$', ',$', ''
			) AS Roles
FROM		@UserRole AS u1
ORDER BY	u1.UserID

-- Stop timing
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

SELECT 
	u1.UserID,
	(
		select case row_number() over (order by (select 1)) when 1 then '' else ', ' end 
			+ t.RoleName AS [text()]
		from (
			select row_number() over (order by min(u2.RoleAssignedDate)) as nr, u2.RoleName
			from @UserRole u2
			where u2.UserID = u1.UserID
			group by u2.RoleName
		) t
		order by t.nr
		for xml path(''), type
	).value('.', 'nvarchar(max)') as Roles
from (
			select 
				UserID
			from @UserRole
			group by
				UserID
		) u1
order by u1.UserID

SELECT dbo.List(myColumn)
FROM myTable
GROUP BY SomeOtherColumn

--An alternative without ROW_NUMBER()
--How about this?
select distinct
	u.UserID
	, stuff((
		select ', ' + u2.RoleName
		from @UserRole u2
		where u2.UserID = u.UserID
		and u2.RoleAssignedDate = (
			select min(u3.RoleAssignedDate)
			from @UserRole u3
			where u3.UserID = u2.UserID
			and u3.RoleName = u2.RoleName
		)
		order by u2.RoleAssignedDate asc
		for xml path('')
	), 1, 2, '') 'RoleName'
from @UserRole u