-- Query 1 have a gander at the data
SELECT
o.*,
p.Name,
x.CurrWeek
FROM tblOrder o
LEFT JOIN tblProduct p ON p.ProductID = o.ProductID
CROSS APPLY (SELECT CurrWeek = DATEPART(week,o.OrderDate)) x
WHERE 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 aggregate
SELECT
p.Name,
[rows] = COUNT(*),
x.CurrWeek
FROM tblOrder o
LEFT JOIN tblProduct p ON p.ProductID = o.ProductID
CROSS APPLY (SELECT CurrWeek = DATEPART(week,OrderDate)) x
WHERE 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 2
SELECT
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 o
LEFT JOIN tblProduct p ON p.ProductID = o.ProductID
CROSS APPLY (SELECT CurrWeek = DATEPART(week,o.OrderDate)) x
WHERE o.OrderDate >= '2013-01-01 00:00:00.000'
AND o.OrderDate < '2014-01-01 00:00:00.000'
GROUP BY p.Name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden