假设航班代码实际匹配，然后使用条件聚合：

select flightcode,
       max(case when col3 = 'SOURCE' then col2 end) as source,
       max(case when col3 = 'TARGET' then col2 end) as target,
       max(SourceAirportCode) as SourceAirportCode
from t
group by flightcode;

您可以使用自联接，并将一个表视为源表，另一个表视为目标表。

WITH flight(FlightCode,  col2,    Col3,    SourceAirportCode)
AS 
(
 SELECT 'Flight007','NYC','SOURCE',64   UNION
 SELECT 'Flight008','ORD','TARGET',87   UNION
 SELECT 'Flight007','SEA','TARGET',NULL UNION
 SELECT 'Flight008','PHX','SOURCE',NULL
)

SELECT f.FlightCode, s.col2 as 'SOURCE', t.col2 as 'TARGET',
       f.SourceAirportCode
FROM flight f
JOIN flight s ON s.Col3 = 'SOURCE' AND s.FlightCode = f.FlightCode
JOIN flight t ON t.Col3 = 'TARGET' AND t.FlightCode = f.FlightCode
WHERE f.SourceAirportCode IS NOT NULL

FlightCode  SOURCE  TARGET  SourceAirportCode
 Flight007  NYC      SEA    64
 Flight008  PHX      ORD    87