-- Query 1 have a gander at the dataSELECT o.*, p.Name, x.CurrWeek FROM tblOrder oLEFT JOIN tblProduct p ON p.ProductID = o.ProductIDCROSS APPLY (SELECT CurrWeek = DATEPART(week,o.OrderDate)) xWHERE o.OrderDate >= '2013-01-01 00:00:00.000' AND o.OrderDate < '2014-01-01 00:00:00.000'-- Query 2 calculate the week number and try aggregateSELECT p.Name, [rows] = COUNT(*), x.CurrWeek FROM tblOrder oLEFT JOIN tblProduct p ON p.ProductID = o.ProductIDCROSS APPLY (SELECT CurrWeek = DATEPART(week,OrderDate)) xWHERE o.OrderDate >= '2013-01-01 00:00:00.000' AND o.OrderDate < '2014-01-01 00:00:00.000'GROUP BY p.Name, x.CurrWeek-- Query 3 solution: set up crosstab, compare results to Query 2SELECT p.Name, week01 = SUM(CASE WHEN x.CurrWeek = 1 THEN 1 END), week02 = SUM(CASE WHEN x.CurrWeek = 2 THEN 1 END), week03 = SUM(CASE WHEN x.CurrWeek = 3 THEN 1 END), week04 = SUM(CASE WHEN x.CurrWeek = 4 THEN 1 END), week53 = SUM(CASE WHEN x.CurrWeek = 53 THEN 1 END), week54 = SUM(CASE WHEN x.CurrWeek = 54 THEN 1 END)FROM tblOrder oLEFT JOIN tblProduct p ON p.ProductID = o.ProductIDCROSS APPLY (SELECT CurrWeek = DATEPART(week,o.OrderDate)) xWHERE o.OrderDate >= '2013-01-01 00:00:00.000' AND o.OrderDate < '2014-01-01 00:00:00.000'GROUP BY p.Name