You can use something like this

WITH CTE AS(

SELECT Product,

SUM( CASE WHEN Category = 'Something' THEN Value ELSE 0 END) AS Total1,

SUM( CASE WHEN Category = 'Something Else' THEN Value ELSE 0 END) AS Total2

FROM YourTable

WHERE Category IN('Something', 'Something Else')

GROUP BY Product

)

SELECT *

FROM CTE;

That's just the basic structure, which you have to understand to be able to complete your requirements.