Table 1-ORIGINAL

uID	firstName	destinationOne	destinationTwo	destinationThree	destinationFour
123	Jim		Alaska
123	Jim		Kansas
123	Jim		Texas
521	Maggy		Virginia
521	Maggy		Idaho
510	Kerry		Florida
510	Kerry		Georgia
510	Kerry		Kansas
981	Dave		California
981	Dave		Washington
981	Dave		Texas
981	Dave		Illinois

Table 1-NEW

uID	firstName	destinationOne	destinationTwo	destinationThree	destinationFour
123	Jim		Alaska			Kansas			Texas
521	Maggy		Virginia		Idaho
510	Kerry		Florida		Georgia		Kansas
981	Dave		California		Washington		Texas			Illinois

DECLARE @Table TABLE
                     ( uid int
                      ,firstName varchar(50)
                      ,destination varchar(50)
                     )

INSERT INTO @Table

 
SELECT 123,	'Jim','Alaska' UNION ALL
SELECT 123,	'Jim','Kansas' UNION ALL
SELECT 123,	'Jim','Texas' UNION ALL
SELECT 521,	'Maggy','Virginia' UNION ALL
SELECT 521,	'Maggy','Idaho' UNION ALL
SELECT 510,	'Kerry','Florida' UNION ALL
SELECT 510,	'Kerry','Georgia' UNION ALL
SELECT 510,	'Kerry','Kansas' UNION ALL
SELECT 981,	'Dave','California' UNION ALL
SELECT 981,	'Dave','Washington' UNION ALL
SELECT 981,	'Dave','Texas' UNION ALL
SELECT 981,	'Dave','Illinois'


 SELECT t2.uid,t2.firstName
       ,[destinationOne]   = MAX(t2.destinationOne)
       ,[destinationTwo]   = MAX(t2.destinationTwo)
       ,[destinationThree] = MAX(t2.destinationThree)
       ,[destinationFour]  = MAX(t2.destinationFour)
 FROM
  (
	select t1.uid
		  ,t1.firstname
		  ,[destinationone] = case when t1.row= 1 then t1.destination else null end
		  ,[destinationtwo] = case when t1.row= 2 then t1.destination else null end
		  ,[destinationthree] = case when t1.row= 3 then t1.destination else null end
		  ,[destinationfour] = case when t1.row= 4 then t1.destination else null end
	 from

 
       (
        select uid,firstName,destination,[Row] = ROW_NUMBER() OVER (partition by uid,firstname order by uid)
        from @table
       ) t1
  ) t2

GROUP BY t2.uid,t2.firstName
ORDER BY uid