title: Pivot query in Microsoft SQL server
body: <p>I am a new to sql server and I have been trying for the last couple of days to transform a simple TRANSFORM query in Access to SQL Server. </p>
  
  <p>In access the query looks like : </p>
  
  <pre><code>TRANSFORM tblDefHolidays.colDate
  SELECT    tblDefHolidays.colDate
  FROM      tblDefHolidays
  WHERE     tblDefHolidays.colDate &gt;= DateAdd ("d", -60, date()) 
            AND tblDefHolidays.colDate &lt;= DateAdd ("yyyy",2, date())
  GROUP BY  tblDefHolidays.colDate
  ORDER BY  tblDefHolidays.colCal DESC 
  PIVOT     tblDefHolidays.colCal;
  </code></pre>
  
  <p>In SQL Server the table tblDefHolidays is defined as follows : </p>
  
  <pre><code>colCal          nvarchar(40)    
  colDate         date    
  colCodeBB       nvarchar(20)    
  colDesc         nvarchar(255)
  </code></pre>
  
  <p>This is my current SQL Server query which is not running. </p>
  
  <pre><code>SELECT colDate, colCal
  FROM 
  (
     SELECT dbo.tblDefHolidays.colDate, dbo.tblDefHolidays.colCal
     FROM   dbInv.dbo.tblDefHolidays) as [subTable]
  PIVOT
  (  max(colDate)
     FOR  dbInv.dbo.tblDefHolidays.colCal
      IN ([OSAKA],[LIFFE],[HKEX],[EUREX],[CME],[CBOE])
  ) as [pivotTable]
  </code></pre>
  
  <p>When running the current sql server query I get :</p>
  
  <blockquote>
    <p>Msg 107, Level 15, State 1, Line 9 The column prefix
    'dbInv.dbo.tblDefHolidays' does not match with a table name or alias
    name used in the query. Msg 207, Level 16, State 1, Line 2 Invalid
    column name 'colDate'.</p>
  </blockquote>
  
  <p>The result (from Access) should look like : </p>
  
  <pre><code> colDate         OSAKA         LIFFE         HKEX        EUREX      CME       CBOE
   7/1/2014                  7/1/2014         
   7/4/2014                  7/4/2014      7/4/2014
   7/21/2014  7/21/2014                   
   8/25/2014  8/25/2014               
   9/1/2014                  9/1/2014      9/1/2014
   9/9/2014                  9/9/2014         
   9/15/2014  9/15/2014                   
   9/23/2014  9/23/2014                   
   10/1/2014                 10/1/2014    
  </code></pre>
  
  <p>Thanks for reading.</p>
  
  <p>Best,</p>
  
  <p>Manu        </p>