Using Sql to Manipulate data a certain way into a new table

Alright, so this is my second time ever asking a question on stackoverflow, and this one I believe is a little complex. If you need any extra information let me know and I will provide it, and thanks for all replies/answers!

Dealer would be the foreign key to the table above. I have looked into pivoting data, but all I have seen is how to convert rows to columns. If anyone has a solution to this it would be greatly appreciated!