here's the data you posted, but cleaned up and in a ready to use format:

the column names in your query did not match the data, and there was no easy way to insert the sample data.

now that it's in a ready-to-consume format, we can start looking at the question itself.

CREATE TABLE table1(

[Pax ID] [int] NULL,

[Reservation] [int] NULL,

[Surname] [varchar](40) NULL,

[First Name] [varchar](40) NULL,

[Pax Type] [char](1) NULL,

[Flight Date] [smalldatetime] NULL,

[Flight Number] [varchar](10) NULL,

[Board] [varchar](3) NULL,

[Off] [varchar](3) NULL,

[Original Booking Date] [smalldatetime] NULL,

[Last Mod Date] [smalldatetime] NULL

)

INSERT INTO table1([Pax ID],[Reservation],[Surname],[First Name],[Flight Date],[Flight Number],[Board],[Off],[Original Booking Date],[Last Mod Date])

SELECT '1558611','899842','SULIMAN','ALI','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558612','899842','ALGANADI','HASAN','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558613','899844','ALYAFEE','MOHMMED','1/1/2013','FO160','SAH','TAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558616','899847','ASSIRI','ahmed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558617','899847','ASSIRI','ahmed','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL

SELECT '1558628','899847','asiri','alin','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558629','899847','asiri','alin','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL

SELECT '1558618','899848','ALAQWAA','EBRAHEEM','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558621','899850','ALGELHM','HAMID','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL

SELECT '1558622','899851','ASGHAR','AMER','1/1/2013','FO205','ADE','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558623','899852','ALHALILI','HAMZAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL

SELECT '1558624','899852','ALJAHDARI','GHALIAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL

SELECT '1558625','899853','ABDULLAH','ADEL','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558626','899854','alasmari','mohammed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558627','899854','alasmari','mohammed','4/1/2013','FO874','ADE','AHB','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO197','AAY','TAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','4/1/2013','FO198','TAI','CAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558624','899851','ASSIRI','ahmed','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013' UNION ALL

SELECT '1558624','899851','ASSIRI','ahmed','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013'

SELECT [Pax ID],[Reservation],[Surname],

STUFF((SELECT ',' + [Surname] FROM [TABLE1] WHERE [Reservation] = t.[Reservation] AND [Surname] <> t.[Surname] FOR XML PATH('')),1,1,'') AS [pax in same res#],

COUNT(1) OVER (PARTITION BY [Reservation]) AS [# of pax],

DATEDIFF(dd,[Purchase date],[Flight date]) AS [Diff flight date purchase date],

STUFF((SELECT DISTINCT ',' + t1.[Off]

FROM [TABLE1] t1

JOIN [TABLE1] t2

ON t1.[Pax ID] = t2.[Pax ID]

AND t1.[Reservation] = t2.[Reservation]

AND t1.[Off] = t2.[Board]

WHERE t1.[Pax ID] = t.[Pax ID]

AND t1.[Reservation] = t.[Reservation]

FOR XML PATH('')

),1,1,'') AS TransitCity,

[Flight date],

STUFF((SELECT ',' + [Flight Number]

FROM [TABLE1]

WHERE [Pax ID] = t.[Pax ID]

AND [Reservation] = t.[Reservation]

FOR XML PATH('')

),1,1,'') AS FLightNo,

(SELECT TOP 1 [Board]

FROM [TABLE1]

WHERE [Pax ID] = t.[Pax ID]

AND [Reservation] = t.[Reservation]

ORDER BY [Flight date] ASC) AS DepartCity,

(SELECT TOP 1 [Off]

FROM [TABLE1]

WHERE [Pax ID] = t.[Pax ID]

AND [Reservation] = t.[Reservation]

ORDER BY [Flight date] DESC) AS ArrivalCity,

[Last Mod Date],

[Purchase Date]

FROM (SELECT [Pax ID],[Reservation],[Surname],MAX([Flight Date]) AS [Flight date],MAX([Original Booking Date]) AS [Purchase Date],MAX([Last Mod Date] ) AS [Last Mod Date]

FROM [TABLE1]

GROUP BY [Pax ID],[Reservation],[Surname]) t