title: Displaying weeks as columns
body: <p>Below is the script to display the number of weeks between the given dates.</p>
  
  <pre><code>SET DATEFIRST 1    
  SELECT ta.account, ta.customer, SUM(amount), DATEPART(ww,ta.dt) WeekNumber
  FROM tablename ta
  WHERE dt &gt;= '12/01/2011 00:00:00' 
        and dt &lt; '12/29/2011 00:00:00'
  GROUP BY ta.account, ta.customer, DATEPART(ww,ta.dt)
  </code></pre>
  
  <p>How do I display the diff weeks as diff columns in my result. Any suggestion would be helpful. </p>
  
  <p>Sample O/P for the above is:</p>
  
  <pre><code>Date    Account         Customer    TotalSeconds    Amount  WeekNumber
  
  2011-11-01  xx0918252   198303792R  394         2.99    45
  2011-11-08  xx1006979   200100567G  92          0.16    46
  2011-11-15  xx1005385   A6863744I   492         1.275   47
  2011-11-21  xx1012872   D7874694G   770         0.52    48
  2011-11-28  xx1006419   C7112151H   1904        2.64    49
  2011-11-28  xx1006420   G7378945A   77          0.3     49
  </code></pre>
  
  <p>I want the O/P like:</p>
  
  <pre><code>Date     Account    Customer    TotalSeconds    Amount  WeekNumber45   WeekNumber46   WeekNumber47   WeekNumber8   WeekNumber49
  </code></pre>
  
  <p>and their corresponding data. Hope u understand my question. Thanks in advance.</p>
  
  <p>Hi All, Thanks for the suggestions n help. Finally, I got the results that i wanted for time being. I still believe that it is hard coding. Is there a better solution for this. Thanks in advance. My code is as follows:</p>
  
  <pre><code>SELECT  ta.account, ta.customer, 
  isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '49' THEN amount END),0) AS "Week49",
  
  isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '50' THEN amount END),0) AS "Week50",
  
  isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '51' THEN amount END),0) AS "Week51",
  
  isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '52' THEN amount END),0) AS "Week52",
  
  isnull(SUM(CASE WHEN DATEPART(ww,ta.dt) = '53' THEN amount END),0) AS "Week53",
  
  FROM (
  select * from tablename
  where dt &gt;= '12/01/2011 00:00:00' and dt &lt;= '12/31/2011 00:00:00'
  ) ta
  group by ta.account, ta.customer
  </code></pre>