问基于Server中的单个列查找源/目标

EN

select min(case when pfid = id then Source end) [Source], 
       max(case when pfid <> id then Destination end) [Destination], PFID 
from table
group by PFID

select distinct first_value(Source) over (partition by PFID order by PFID) , last_value(Destination) over (partition by PFID order by PFID), PFID 
from table
order by PFID

SELECT q.PFID,p1.Source,p2.Destination
FROM
  (
    SELECT PFID,MIN(ID) SourceID,MAX(ID) DestinationID
    FROM [Your Table]
    GROUP BY PFID
  ) q
JOIN [Your Table] p1 ON p1.ID=q.SourceID
JOIN [Your Table] p2 ON p2.ID=q.DestinationID

SELECT DISTINCT
  PFID,
  FIRST_VALUE(Source)OVER(PARTITION BY PFID ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Source,
  LAST_VALUE(Destination)OVER(PARTITION BY PFID ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Destination
FROM [Your Table]

;WITH cte AS(
  -- start point
  SELECT ID,Source,Destination,PFID,1 step
  FROM [Your Table]
  WHERE PFID=ID

  UNION ALL

  -- next point
  SELECT t.ID,c.Source,t.Destination,t.PFID,c.step+1
  FROM [Your Table] t
  JOIN cte c ON t.Source=c.Destination
)
SELECT PFID,Source,Destination
FROM 
  (
    SELECT
      PFID,
      Source,
      Destination,
      step,
      MAX(step)OVER(PARTITION BY PFID) LastStep
    FROM cte
  ) q
WHERE step=LastStep

SELECT s.PFID,s.Source,d.Destination
FROM
  (
    SELECT PFID,MIN(CASE WHEN PFID=ID THEN Source END) Source,MAX(ID) DestinationID
    FROM [Your Table]
    GROUP BY PFID
  ) s
JOIN [Your Table] d ON d.ID=s.DestinationID