sql server - SQL - Column per week (X weeks from declared start date) -

sql server - SQL - Column per week (X weeks from declared start date) -

this question has reply here:

sql server: dynamic pivot headers include column name , date 1 reply

i need run select statement returns number of invoices values showing in column that's appropriate week invoice appeared in. example:

id value invoiced 1 150 2014-01-06 2 220 2014-01-13 3 190 2014-01-13 4 880 2014-01-27 5 325 2014-02-03

i need info display in format similar below:

id w/e 2014-01-05 w/e 2014-01-12 w/e 2014-01-19 w/e 2014-01-26 w/e 2014-02-02 w/e 2014-02-09 w/e 2014-02-16 1 0 150 0 0 0 0 0 2 0 0 220 0 0 0 0 3 0 0 190 0 0 0 0 4 0 0 0 0 880 0 0 5 0 0 0 0 0 325 0

with first date beingness taken variable , remaining weeks beingness calculated point on (for x weeks). initial thought utilize temporary table hold rows each of date ranges , bring together table perform pivot... honest pretty new me have absolutely no thought of syntax.

there no requirement grouping (no sums deal with), need create sure appropriate values end in appropriate columns.

p.s. know loops on presentation layer, in particular case, there isn't 1 such.

declare @t table (id int,value int,invoiced date ) insert @t (id,value,invoiced)values (1,150,'2014-01-06') insert @t (id,value,invoiced)values (2,220,'2014-01-13') insert @t (id,value,invoiced)values (3,190,'2014-01-15') insert @t (id,value,invoiced)values (4,880,'2014-01-27') insert @t (id,value,invoiced)values (5,325,'2014-01-29') select id, [w/e2014-01-06], [w/e2014-01-13], [w/e2014-01-15], [w/e2014-01-27], [w/e2014-01-29] (select distinct t.id,t.value, 'w/e' + cast(t.invoiced varchar)as dateno @t t)t pivot (max(value)for dateno in([w/e2014-01-06],[w/e2014-01-13],[w/e2014-01-15],[w/e2014-01-27],[w/e2014-01-29]))p

sql sql-server