If you can ease up on the requirement of how exactly the data will look, the problem is quite simple. You'll need to rewrite your query to be something like:
SUM([Amount]) AS [Amount],
RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]
You'll get data in the following format:
material | amount | date
From there, you'll just have to pivot the data in C# to a form you can use. Aside from the above, the other options you have would be:
-Dynamic SQL (i.e. SQL that writes SQL statements and executes it)
] - I don't consider this much different than Dynamic SQL, since you have to define what each column is, but I just wanted to state it.