How do I SELECT INTO new columns? I'm trying to write a query that will put values in three different columns. I have the query pulling the information I need, I just don't know how to split the values out into different columns. 

The first column value should display the values where financialinst.name = National Financial Services

The second column value should display the values where financialinst.name is not equal to National Financial Services (DirectValue)

The last column value should be the total of these two columns (National Financial Services + Direct Values) 

Here is what I have so far:

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 ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE     (Account.Closed &lt;&gt; 'Y') AND (Asset.Value &gt; '0')
GROUP BY Household.Name, Account.AccountNumber,  FinancialInst.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode, Account.Registration, 
                      Account.Description
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.