ROLE	FROM		TO
1	1/1/2012	8/8/2012
2	2/2/2012	5/5/2012
3	6/6/2012	NULL

ROLES	FROM		TO
1	1/1/2012	2/2/2012
1, 2	2/2/2012	5/5/2012
1	5/5/2012	6/6/2012
1, 3	6/6/2012	8/8/2012
3	8/8/2012	NULL

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		[Role] INT NOT NULL,
		[From] DATE NOT NULL,
		[To] DATE NULL
	);

INSERT	@Sample
	(
		[Role],
		[From],
		[To]
	)
VALUES	(1, '20120101', '20120808'),
	(2, '20120202', '20120505'),
	(3, '20120606', NULL);

-- Solution by SwePeso
;WITH cteSource(theDate)
AS (
	SELECT	u.theDate
	FROM	@Sample AS s
	UNPIVOT	(
			theDate
			FOR theColumn IN (s.[From], s.[To])
		) AS u
)
SELECT		STUFF(w.Data, 1, 2, '') AS Roles,
		s.theDate AS [From],
		f.theDate AS [To]
FROM		cteSource AS s
OUTER APPLY	(
			SELECT TOP(1)	x.theDate
			FROM		cteSource AS x
			WHERE		x.theDate > s.theDate
			ORDER BY	x.theDate
		) AS f(theDate)
OUTER APPLY	(
			SELECT DISTINCT	', ' + CAST(q.[Role] AS VARCHAR(12))
			FROM		@Sample AS q
			WHERE		s.theDate >= q.[From]
					AND (s.theDate < q.[To] OR q.[To] IS NULL)
			ORDER BY	', ' + CAST(q.[Role] AS VARCHAR(12))
			FOR XML		PATH('')
		) AS w(Data)
ORDER BY	s.theDate;

CREATE VIEW dbo.vwWhatever
AS

WITH cteSource(theDate)
AS (
	SELECT	u.theDate
	FROM	@Sample AS s
	UNPIVOT	(
			theDate
			FOR theColumn IN (s.[From], s.[To])
		) AS u
)
SELECT		STUFF(w.Data, 1, 2, '') AS Roles,
		s.theDate AS [From],
		f.theDate AS [To]
FROM		cteSource AS s
OUTER APPLY	(
			SELECT TOP(1)	x.theDate
			FROM		cteSource AS x
			WHERE		x.theDate > s.theDate
			ORDER BY	x.theDate
		) AS f(theDate)
OUTER APPLY	(
			SELECT DISTINCT	', ' + CAST(q.[Role] AS VARCHAR(12))
			FROM		@Sample AS q
			WHERE		s.theDate >= q.[From]
					AND (s.theDate < q.[To] OR q.[To] IS NULL)
			ORDER BY	', ' + CAST(q.[Role] AS VARCHAR(12))
			FOR XML		PATH('')
		) AS w(Data);