sql server - SQL Get Weekly Count of product items -

i trying total number entries table per week per product. every time orders product, entered database following information:

table: orders

order_id order order_date 

there 8 options "order", pre-set , never changing. need see count per week of items in table..

i want see this:

 order     week 1    week 2    week 3    week 4 etc...   order1    30         10       11         23  order2    40          4        0         44  order3    88         23       12         22 

can tell me how achieved??

thanks.. i'm stuck on this.

as nickyvv indicated want pivot. sql server cannot accept dynamic columns in pivot, have spell them out. don't want order_id in first column, since 1s , 0s in counts, product_id instead. this:

select  product_id, [week 1],[week 2],[week 3],[week 4],[week 5]         (select          product_id,         'week ' + cast(datepart(wk,[order_date]) varchar(2)) weekname         [orders]) o pivot (count(weekname)  weekname in ([week 1],[week 2],[week 3],[week 4],[week 5])) p 

unfortunately, you'll have spell out possible week names. there ways using dynamic sql, wouldn't recommend them rather complex , nasty. also, remember if going report, pivoting in reporting application (ssrs, etc.) instead of in db.