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:
SELECT
material,
SUM([Amount]) AS [Amount],
RIGHT(CONVERT(varchar(50), [date_in], 106), 8) AS [Date]
FROM
[rec_stats]
GROUP BY
material,
YEAR([date_in]),
MONTH([date_in])
ORDER BY
material,
[date_in]
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)
-
SQL Pivot[
^] - 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.