title: MS SQL Server pivot table with subquery in column clause
body: <p>Im sure this is a simple technique although I can't find an answer so far!</p>
  
  <p>I have       </p>
  
  <pre><code>TIMESTAMP           | POINTNAME | VALUE
  2012-10-10 16:00:00   AHU01       20
  2012-10-10 16:00:00   AHU02       25
  2012-10-10 16:00:15   AHU01       26
  2012-10-10 16:00:15   AHU02       35
  </code></pre>
  
  <p>etc... ( for approx 800 POINTNAMES)  </p>
  
  <p>with many pointnames I dont want to list each one in the 'IN' clause of the pivot 'FOR'
  (as syntax given below) definition but would like to use perhaps a subquery.</p>
  
  <p>So what I would like is all the POINTNAME values as columns with A TIMESTAMP AND VALUE column, so I will get one TIMESTAMP value and many columns with each POINTNAME, there is only one value per POINTNAME PER TIMESTAMP so I don't need to aggregate anything so just choose max anyway?</p>
  
  <p>Something like:</p>
  
  <pre><code>SELECT [TIMESTAMP] FROM ( SELECT * FROM POINT_TABLE)
  PIVOT( Max[Value] FOR [POINTNAME] IN (SELECT DISTINCT [POINTNAME] FROM POINT_TABLE)
  </code></pre>
  
  <p>would produce-</p>
  
  <pre><code>   TIMESTAMP              AHU01          AHU02
   2012-10-10 16:00:00        20             25
   2012-10-10 16:15:00        26             35
  </code></pre>
  
  <p>I realise it is probably no this simple but hopefully you get what I'm trying to achieve?</p>
  
  <p>PIVOT SYNTAX:</p>
  
  <pre><code>SELECT &lt;non-pivoted column&gt;,
      [first pivoted column] AS &lt;column name&gt;,
      [second pivoted column] AS &lt;column name&gt;,
      ...
      [last pivoted column] AS &lt;column name&gt;
  FROM
      (&lt;SELECT query that produces the data&gt;) 
      AS &lt;alias for the source query&gt;
  PIVOT
  (
      &lt;aggregation function&gt;(&lt;column being aggregated&gt;)
  FOR 
  [&lt;column that contains the values that will become column headers&gt;] 
      IN ( [first pivoted column], [second pivoted column],
      ... [last pivoted column])
  ) AS &lt;alias for the pivot table&gt;
  &lt;optional ORDER BY clause&gt;;
  </code></pre>