select year(o.OrderDate),
(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='1') as "Beverages",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='2') as "Condiments",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='3') as "Confections",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='4') as "Dairy Products",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='5') as "Grains/Cereals",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='6') as "Meat/Poultry",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='7') as "Produce",

(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p where
o1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) and
p.CategoryID='8') as "Seafood"

from
Orders o
group by year(o.OrderDate)