Based upon your illustration, I think the answer you are looking for is a qualified "sort of". What it looks like you want is a new row with just the last column's data when the first part is duplicated. Sure, you could blank the other columns out but what's the point. Besides, the SQL statement to do so would be ugly. However, if you want all of the values for that last column to be in one column (so each person gets one row and the last column has n number of entries in it), you can do so using a CROSS APPLY statement. Here is a forum post that will help you out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102429[
^]
The statement from that post that would probably be what you want is this one:
SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM #TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
FROM #TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH('')) el(EmpList)
Obviously you would need to modify it to fit your needs but the end result would be a comma-delimited list of values in the last column.