I have the following query select product.Name, product.SizeId, orderdetail.Tal as Total from Product inner join Orderdetail on product.ProductId = orderdetail.ProductId inner join Orders on orderdetail.OrderId = orders.orderid where Orders.Orderdate BETWEEN '20130701' AND '20131231' and product.CategorieId like '%adventure'
that gives as result:vgv 80 A 1vgv 85 B 1vgv 80 A 1tail 75 C 1tail 85 B 1tail 85 B 1the result I want = product.sizeid 75 C 80 A 85 Bproduct.Name vgv 0 2 1product.Name tail 1 0 2Sort of cross table query.I'll tried Pivot in sql server:How do I make this work?I tried:select product.Name, product.SizeId as maat from(select product.Name, product.SizeId, orderdetail.Tal as Aantal from product inner join Orderdetail on product.ProductId = orderdetail.ProductId inner join Orders on orderdetail.OrderId = orders.orderid where Orders.Orderdatum BETWEEN '20130701' AND '20131231' and product.CategorieId like '%aventure' )pivot(sum(orderdetail.tal) for SizeId in SizeId) as pivoting
But this doesn't work.if you do not try, it will not work
should I use PIVOT?
Posted - 2014-02-22 : 16:42:02
