title: T-SQL Pivot query
body: <p>I have a table that contains a laserform name and a date e.g.</p>
  
  <p>My table is called myBWB_LaserForms</p>
  
  <pre><code>laserform   editwhen
  LRAP1.lpw   2009-09-03 16:06:00.000
  CON29R.lpw  2009-10-29 12:35:00.000
  UN1.lpw     2010-03-02 11:38:00.000
  UN244.lpw   2012-04-19 12:14:00.000
  LT3.lpw     2013-01-02 11:00:00.000
  LRAP1.lpw   2007-12-10 15:34:00.000
  </code></pre>
  
  <p>What I am trying to produce is a report which has the name of the laserform down the left and years across the top e.g. 2004, 2005, 2006 and then each intersecting cell would have a count of the number of records for that form name for that particular year.</p>
  
  <p>Normally I would do something like</p>
  
  <pre><code>select l.laserform,
  (select count(*) from mybwb_laserforms l1 where l1.laserform=l.laserform and year(editwhen)=2004) AS [2004],
  (select count(*) from mybwb_laserforms l2 where l2.laserform=l.laserform and year(editwhen)=2005) AS [2005],
  (select count(*) from mybwb_laserforms l3 where l3.laserform=l.laserform and year(editwhen)=2006) AS [2006],
  (select count(*) from mybwb_laserforms l4 where l4.laserform=l.laserform and year(editwhen)=2007) AS [2007]
  from mybwb_laserforms l
  group by l.laserform
  order by l.laserform
  </code></pre>
  
  <p>However I realise that this is pretty horrible SQL and I should be much more intelligent about it.</p>
  
  <p>I've been trying to learn how to use the pivot function but am struggling to get my head around it.</p>
  
  <p>Also most examples I have found seem to have the columns of the query hard coded - it would be good if the pivot could be dynamic and get the unique year(editwhen) values so I don't have to modify the query next year and the year after that.</p>