select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,
ABS(A7.GrossAmount) as 'Gross Payment Plan Amount' 

from Transactions
  inner join Person on Transactions.EntityID = Person.ID
  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID
  inner join Student on Person.ID = Student.ID
  right join Enrolment on Student.ID = Enrolment.StudentID 
  inner join Stream on Enrolment.StreamID = Stream.ID 
  INNER JOIN Transactions AS A7 ON (ENTITYID=Person.ID) 
  
WHERE A7.GroupType='Allocation' AND LinkType <> 'Payment'

------解决方案--------------------
SQL code

select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,
ABS(ISNULL(A7.GrossAmount,0)) as 'Gross Payment Plan Amount' 

from Transactions
  inner join Person on Transactions.EntityID = Person.ID
  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID
  inner join Student on Person.ID = Student.ID
  right join Enrolment on Student.ID = Enrolment.StudentID 
  inner join Stream on Enrolment.StreamID = Stream.ID 
  LEFT INNER JOIN Transactions AS A7 ON (ENTITYID=Person.ID) 
  
WHERE A7.GroupType='Allocation' AND LinkType <> 'Payment'

------解决方案--------------------
是有一定的难度啊

------解决方案--------------------
SQL code

select Person.ID, Person.Surname, Person.Firstname, Stream.Name AS StreamName, PaymentPlan.Name,
  (select top 1 ABS (Transactions.GrossAmount)  
  from Transactions
  where GroupType = 'Allocation'  
  and LinkType <> 'Payment'and entityid = Person.ID) as 'Gross Payment Plan Amount'
from Transactions
  inner join Person on Transactions.EntityID = Person.ID
  inner join PaymentPlan on Transactions.GroupID = PaymentPlan.ID
  inner join Student on Person.ID = Student.ID
  right join Enrolment on Student.ID = Enrolment.StudentID  
  inner join Stream on Enrolment.StreamID = Stream.ID