Here you go. l leave the easy part (eliminate unwanted rows) for you to solve the tough part can be done this way:
CREATE TABLE #DATA (ManagerID INT, EmployeeID VARCHAR(50));
;WITH TEMPTE(ManagerID,EmployeeID) AS(
SELECT '1001','9990' UNION ALL
SELECT '1001','9991' UNION ALL
SELECT '1002','9993' UNION ALL
SELECT '1002','9994' UNION ALL
SELECT '1003','9995' UNION ALL
SELECT '1003','9996' UNION ALL
SELECT '1003','9997' UNION ALL
SELECT '1003','9998' UNION ALL
SELECT '1003','9999')
INSERT INTO #DATA (ManagerID, EmployeeID)
SELECT ManagerID, EmployeeID
FROM TEMPTE;
CREATE TABLE #TEMP (EmployeeID VARCHAR(50));
INSERT INTO #TEMP(EmployeeID)
SELECT DISTINCT EmployeeID AS EmployeeID
FROM #DATA
ORDER BY EmployeeID;
DECLARE @SQL AS VARCHAR(8000)
SET @SQL = 'SELECT MANAGERID ';
SELECT @SQL = @SQL + ',[Employee' + EmployeeID+'] =ISNULL((SELECT EmployeeID FROM #DATA WHERE EmployeeID ='+ EmployeeID+ '),NULL)'
FROM #TEMP
ORDER BY EmployeeID;
PRINT @SQL + 'FROM #DATA T1'
EXEC( @SQL + 'FROM #DATA T1')