title: Pivot/Cross tab results on PHP/SQL query
body: <p>Running most recent version of PHP under most recent build of WAMPserver.</p>
  
  <p>Database is SQL Server 2005</p>
  
  <p>Here is the query I am currently running that works. What I want to do is change the output from:</p>
  
  <pre><code>CorrectionsCount , Employee  , Date    
  1                , Joe       , 02/12/2012
  31               , Barbara   , 02/13/2012    
  12               , Paula     , 02/16/2012
  </code></pre>
  
  <p>To something like this</p>
  
  <pre><code>[EMPLOYEE NAME], 02/12/2012,    02/13/2012,    02/16/2012    
  Joe              , 31         , 0              , 0    
  Barbara          , 0          , 31             , 0   
  Paula            , 0          , 0              , 12
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>&lt;?php
  
  // connect to DSN 
  $DP2connect = odbc_connect("DB", "USER", "PWORD") or die ("Could not connect to 
  
  server");
  
  $DP2query = "
  SELECT SUM(CorrectionsCount),CorrectionUser,
  convert(char(10), DateHourCorrected, 120)
  FROM ISIImageCorrections
  WHERE DateHourCorrected BETWEEN '$theDate1' AND '$theDate2'
  GROUP BY CorrectionsCount,CorrectionUser,DateHourCorrected
  ";
  
  
  $DP2result2 = odbc_exec($DP2connect, $DP2query);
  
  odbc_result_all($DP2result2, 'id="results"');
  ?&gt;
  </code></pre>
  
  <p>$thedate1 and $thedate2 are variables being POSTed to this page by a calendar picker from another PHP page.</p>
  
  <p>UPDATE: I tried running the COALESCE function described below, but it generates a syntax error near the keyword 'FROM' (the second instance of "FROM" in this statement). Am I inputting it properly?</p>
  
  <pre><code>$DP2connect = odbc_connect("dp2_database", "DP2", "DP2Express!") or die ("Could not connect to 
  
  server");
  
  $DP2query = 
  "WITH T 
  AS(
      SELECT CorrectionUser, CorrectionsCount, DateHourCorrected
      FROM ISIImageCorrections
  )
  SELECT CorrectionUser, 
         COALESCE([02/12/2012], 0) AS [02/12/2012],
         COALESCE([02/13/2012], 0) AS [02/13/2012],
         COALESCE([02/16/2012], 0) AS [02/16/2012],
  FROM T
  PIVOT(SUM(CorrectionsCount) FOR [Date] IN([02/12/2012], [02/13/2012], [02/16/2012])) AS P";
  
  
  $DP2result2 = odbc_exec($DP2connect, $DP2query);
  
  odbc_result_all($DP2result2, 'id="results"');
  </code></pre>