How do I SELECT INTO new columns?
This would be a good application of an aggregate function and a case statement:

    ;WITH a AS -- CTE for your query
    (SELECT Household.Name, 
            Account.AccountNumber, 
            Account.Registration, 
            FinancialInst.Name AS [Financial Institution], 
            SUM(Asset.Value) AS Value, 
            FinancialPlanner.LastName, 
            PlanFirmSpecCode.SpecialCode,
            NFSvsDirect=CASE WHEN FinancialInst.Name ='National Financial Services' THEN 'NFS' 
            ELSE 'Direct Business' END
FROM PlanFirmSpecCode 
INNER JOIN FinPlanSpecCode 
   ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID 
RIGHT OUTER JOIN Household 
INNER JOIN Account 
   ON Household.HouseholdID = Account.HouseholdID 
INNER JOIN Asset 
   ON Account.AccountID = Asset.AccountID 
INNER JOIN FinancialPlanner 
   ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID 
LEFT OUTER JOIN FinancialInst 
   ON Account.FinancialInstID = FinancialInst.FinancialInstID 
INNER JOIN /*inner join not specified in question, assumed*/ FinPlanSpecCode      
   ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE (Account.Closed &lt;&gt; 'Y') 
  AND (Asset.Value &gt; '0')
GROUP BY Household.Name, 
            Account.AccountNumber, 
            Account.Registration, 
            FinancialInst.Name ,
            FinancialPlanner.LastName, 
            PlanFirmSpecCode.SpecialCode,
            CASE WHEN FinancialInst.Name ='National Financial Services'THEN 'NFS' ELSE 'Direct Business' END

)

        SELECT SUM(CASE WHEN [Financial Institution] = 'National Financial Services' THEN Value ELSE 0 END) as [FirstColumn], 
                   SUM(CASE WHEN [Financial Institution] &lt;&gt; 'National Financial Services' THEN Value ELSE 0 END) as [SecondColumn],
                   SUM(Value) as [ThirdColumn]
            From a


Please let me know if there's any need to clarify further.