DECLARE @User TABLE (UserID int, UserName varchar(50))
INSERT INTO @User
SELECT 100, 'John Smith'
UNION SELECT 200, 'Amy Jones'
UNION SELECT 300, 'Ed Wilson'
UNION SELECT 400, 'Michael Nielsen'
UNION SELECT 500, 'Tyler Jacobs'

DECLARE @TrainCat TABLE (TrainID int, TrainName varchar(50), Enabled bit)
INSERT INTO @TrainCat
SELECT 100, 'Test100', 1
UNION SELECT 200, 'Test200', 1
UNION SELECT 300, 'Test300', 1
UNION SELECT 400, 'Test400', 1
UNION SELECT 500, 'Test500', 1
UNION SELECT 600, 'Test600', 0

DECLARE @UserTrain TABLE (UserID int, TrainID int, DateCompleted datetime)
INSERT INTO @UserTrain
SELECT 100,100,getdate() -4
UNION SELECT 200,100,getdate() -4
UNION SELECT 400,100,getdate() -4
UNION SELECT 500,100,getdate() -4
UNION SELECT 200,200,getdate() -3
UNION SELECT 300,200,getdate() -3
UNION SELECT 500,200,getdate() -3
UNION SELECT 100,300,getdate() -2
UNION SELECT 400,300,getdate() -2
UNION SELECT 500,300,getdate() -2
UNION SELECT 100,400,getdate() -1
UNION SELECT 200,400,getdate() -1
UNION SELECT 300,400,getdate() -1
UNION SELECT 400,400,getdate() -1
UNION SELECT 500,400,getdate() -1
UNION SELECT 100,500,getdate()
UNION SELECT 300,500,getdate()
UNION SELECT 400,500,getdate()
UNION SELECT 500,500,getdate()

/* Tweaking Seventhnight's example from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46295 */

DECLARE @MyTable TABLE(UserID int
	, UserName varchar(50)
	, TrainId int
	, TrainName varchar(50)
	, DateCompleted datetime
	, list nvarchar(500))

DECLARE @MyList nvarchar(500)
,	@lastKey int

SET @lastKey = NULL

INSERT INTO @MyTable
SELECT	u.UserID
,	u.UserName
,	ut.TrainID
,	t.TrainName
,	ut.DateCompleted
FROM 	@User u CROSS JOIN
	@TrainCat t LEFT JOIN
	@UserTrain ut ON ut.UserID = u.UserID AND ut.TrainID = t.TrainID
WHERE t.Enabled = 1
ORDER BY u.UserName, u.UserID, t.TrainID

UPDATE @MyTable
SET
	@MyList = CASE WHEN ISNULL(@lastKey,UserID+1)<>UserID 
		THEN '''' + UserName + ''' AS [User], ' +
			CASE WHEN TrainID IS NULL THEN '''---NOT COMPLETED---'' AS [' + TrainName + '], '
				ELSE '''' + TrainName + ''' AS [' + TrainName + '], ' END +
			'''' + ISNULL(CAST(DateCompleted AS nvarchar(50)), '') + ''' AS [Date Completed]'
		ELSE @MyList + ', ' + 
			CASE WHEN TrainID IS NULL THEN '''---NOT COMPLETED---'' AS [' + TrainName + '], '
				ELSE '''' + TrainName + ''' AS [' + TrainName + '], ' END +
			'''' + ISNULL(CAST(DateCompleted AS nvarchar(50)), '') + ''' AS [Date Completed]' END
,	@lastKey = UserID
,	list = @MyList

FROM @MyTable

DECLARE @Sql nvarchar(4000)

SELECT @Sql = COALESCE(@Sql + ' UNION SELECT ', 'SELECT ') + list
FROM (
	SELECT Z.list
	FROM @MyTable Z
	INNER JOIN (
		SELECT UserID, listLen = MAX(LEN(list)) FROM @MyTable GROUP BY UserID
		) Y ON Z.UserId = Y.UserId AND LEN(Z.list) = Y.listLen
	) t

EXEC (@Sql)