SQL PIVOT on Multiple Columns in SQL Server

I have a table that contains investor data. For each investor data is being inserted with Investor, Investment_Date, Account_Number, Realized_Gain_Loss, Unrealized_Gain_Loss, Total_Income. For each investor there are 3 rows. Investor, Investment_Date, Account_Number remain consistent for each investor. I want to display Realized_Gain_Loss, Unrealized_Gain_Loss, Total_Income on the same line. Below is the SQL to create a sample table with data. There are 2 investors and a total of 6 rows. I want to consolidate this into 2 lines which is 1 for each investor.

I am having so difficulty getting this to work as a SQL Pivot and I would appreciate any possible assistance. Thanks in advance!

Screenshot of Desired Formatting

create table #Investor_Account_Balances
(      
Investor varchar(100),
Investment_Date      datetime,
Account_Number int,
Realized_Gain_Loss decimal (20,2),
Unrealized_Gain_Loss decimal (20,2),
Total_Income decimal (20,2)
)

insert into #Investor_Account_Balances (
                                                            Investor,
                                                            Investment_Date,
                                                            Account_Number,
                                                            Realized_Gain_Loss,
                                                            Unrealized_Gain_Loss,
                                                            Total_Income
                                                            )
values('Robert Jones Foundation',      '20171201',      1234, NULL,      NULL,      -1009.99),
           ('Robet Jones Foundation',      '20171201',      1234,      NULL,      -847.85,      NULL),
           ('Robert Jones Foundation',      '20171201',      1234,      -1507.92,      NULL,      NULL),
         ('Elmira Johnson Trust',          '20171201',      4321,      NULL,      NULL, -333.00),
         ('Elmira Johnson Trust',          '20171201',      4321,        NULL,      -334.98,   NULL),
         ('Elmira Johnson Trust',          '20171201',      4321,       -595.76,  NULL, NULL)

select * from  #Investor_Account_Balances

drop table #Investor_Account_Balances
Rob M.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SELECT Investor, Investment_Date, Account_Number,
      SUM(Realized_Gain_Loss) AS Realized_Gain_Loss,
      SUM(Unrealized_Gain_Loss) AS Unrealized_Gain_Loss,
      SUM(Total_Income) AS Total_Income
FROM #Investor_Account_Balances
GROUP BY Investor, Investment_Date, Account_Number
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Can we see the existing query please?
Probably can fix this issue before placing the results it into  #Investor_Account_Balances
0
Rob M.Author Commented:
Grouping makes sense. Silly question on my part. Sorry. Was not able to group on previous try. On slightly deeper thought the NULL values were grouped causing the entries to break out due to grouping on all columns including Realized, Unrealized and Total Income columns.

Thanks very much for the quick responses from both Scott and PortletPaul.
0
Rob M.Author Commented:
Thanks Scott. Much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.