Scenario Based SQL Script

Scenario:
Table A contain columns like PledgeId, CodeType, Codevalue. etc.,
(In the below script, table A name is "ConstituentPledgeCode")


Table B contain columns like PledgeId, etc.,
(In the below script, table B name is "ConstituentPledge")


Requirement is:
Create 3 new columns in ConstituentPledge to roll the ConstituentPledgeCode data up (in EDW, ListSelect20. 
  A. RecurringType (if multiple, select the first one) 
     Look for CodeType = 'RECUR' and IsActive = 1... load the code value into RecurringType 
  B. FriendsFamStatementPreference (if multiple, select the first one) 
     Look for CodeType = 'STMNTPREF' and IsActive = 1... load the code value into FriendsFamStatementPreference 
  C. CLinkStatementPreference (if multiple, select the first one) 
     Look for CodeType = 'CLSTMNTPRF' and IsActive = 1... load the code value into CLinkStatementPreference 




SELECT  ap.[RecordId] as [ConstituentPledgeKey]
      ,cd.[ConstituentDefinitionId]
      ,ap.[PledgeCode]
      ,ap.[PledgeId]
      ,pm.PledgeMasterId
      ,[TotalAmount]
      ,[AmountPerGift]
      ,[PledgeFrequency]
      ,[NumberPledged]
      ,ap.[StartDate]
      ,ap.[EndDate]
      ,[OpenEnded]
      ,[PledgeStatus]
      ,sc.SourceCodeId
      ,[CurrencyCode]
      ,[CancelReasonCode]
      ,[CancelDate]
      ,ap.[ResponseId]
 ,trm.[TransactionResponseMasterId]
      ,[NbrOfAdjustmentMonths]
      ,[AcctNbrForPledgeStmtAndAck]
      ,[AcctNbrForPledgeInfoUpdates]
      ,[OriginalPledgeId]
      ,ap.[RecordId]
      ,ISNULL(RT.CodeValue,'') AS RecurringType 
 ,ISNULL(FFSP.CodeValue,'') AS FriendsFamStatementPreference 
 ,ISNULL(CLP.CodeValue,'') AS CLinkStatementPreference
FROM [dbo].s_A10_AccountPledges ap
INNER JOIN ConstituentDefinition cd  ON cd.AccountKey = ap.AccountNumber
LEFT JOIN PledgeMaster pm on pm.PledgeCode = ap.PledgeCode
LEFT JOIN SourceCode sc on sc.SourceCode = ap.SourceCode
LEFT JOIN TransactionResponseMaster trm ON trm.[ResponseId] = ap.[ResponseId]
LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'RECUR' and Active = 1
) RT on RT.PledgeId = ap.PledgeId


LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'STMNTPREF' and Active = 1
) FFSP on FFSP.PledgeId = ap.PledgeId


LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'CLSTMNTPRF' and Active = 1
) CLP ON CLP.PledgeId = ap.PledgeId
WHERE ap.Operation = 'M' AND ap.[RecordExists]  IS NULL