title: How to change column name based on given month in sql server 2008R2
body: <p>i want to show my pivot table column name as <code>Monday 1</code>,<code>Tuesday 2</code> based on the month from  the given date...i.e if it is July the month then <code>Monday 1</code> <code>Tuesday 2</code> so on for all days of the month.. 
  this is my coding how to achieve this </p>
  
  <pre><code>set @query='SELECT 
          P.tenant_id,CI.companyname,BM.brandname,bl.buildingname,T.Unitname,tm.tradecategoryname,l.locationname,T.adsrid,T.carpetarea,T.chargeablearea,
          P.[1],P.[2],P.[3],P.[4],P.[5],P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17],
          P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],P.[24],P.[25],P.[26],P.[27],P.[28],P.[29],P.[30],P.[31]
          FROM
          (
          select tenant_id,Day1=day(RECEIPT_DATE),Net_Amt from Daywiseconsolidate WITH(NOLOCK)
           where tenant_id in (' + @filter + ') and month(receipt_date)='+ convert(nvarchar(2),month(@fromdate)) +' and year(receipt_date)='+convert(nvarchar(4),year(@fromdate))+'
      ) A 
      PIVOT 
      (
      SUM(NET_AMT) 
      FOR DAY1 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],
      [18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
      ) AS P
      inner join tenant T on P.tenant_id=T.adsrid
      left outer join locationmaster l on t.locationId=l.locationId
      left outer join companyinfo CI on T.companyid=CI.companyid
      left outer join brandmaster BM on T.brandid=BM.brandid
      left outer join buildingmaster bl on t.buildingid=bl.buildingid
      left outer join tradecategorymaster tm on T.tradecategory=tm.tradecategoryid 
      where bl.active=''Y'' and tm.active=''Y'''
  </code></pre>
  
  <p><strong>i tried like this for day 1 column as Monday 1 but it didnt woked!!</strong></p>
  
  <pre><code>SELECT 
  P.tenant_id,CI.companyname,BM.brandname,bl.buildingname,T.Unitname,tm.tradecategoryname,
  l.locationname,T.adsrid,T.carpetarea,T.chargeablearea,
  **P.[1] as datename(dw,dateadd(dd,-(day(@FromDate)-1),@FromDate))+' '+datename   (day,dateadd(dd,-(day(@FromDate)-1),@FromDate))**,
  P.[2],P.[3],P.[4],P.[5],
  P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17],
  P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],P.[24],P.[25],P.[26],P.[27],P.[28],P.[29],
  P.[30],P.[31]
  
  FROM
  (
  select tenant_id,Day1=day(RECEIPT_DATE),Net_Amt from Daywiseconsolidate WITH(NOLOCK)
  where tenant_id in (select adsrid from tenant where active='Y' ) and 
  month(receipt_date)=9 and year(receipt_date)=2011
  ) A 
  
  PIVOT 
  (
  SUM(NET_AMT) 
  FOR DAY1 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
  [17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
  ) AS P
  
  inner join tenant T on P.tenant_id=T.adsrid
  left outer join locationmaster l on t.locationId=l.locationId
  left outer join companyinfo CI on T.companyid=CI.companyid
  left outer join brandmaster BM on T.brandid=BM.brandid
  left outer join buildingmaster bl on t.buildingid=bl.buildingid
  left outer join tradecategorymaster tm on T.tradecategory=tm.tradecategoryid 
  where bl.active='Y' and tm.active='Y'
  </code></pre>
  
  <p>could you please help me to get those desired columns</p>