title: SQL Server dynamic Pivot with datetime
body: <p>I'm trying to pivot <code>join_date</code> (<code>datetime</code>) column and split it by year + month and group by <code>mtype</code> and it must be dynamic as the date will change.  </p>
  
  <p>TABLE</p>
  
  <pre><code>+-------+------------------+
  | Mtype |    join_date     |
  +-------+------------------+
  | A     | 28/05/2014 00:00 |
  | B     | 04/11/2014 00:00 |
  | C     | 23/03/2007 00:00 |
  | D     | 04/09/2013 00:00 |
  | E     | 26/11/2014 00:00 |
  | F     | 24/07/2011 00:00 |
  | G     | 28/05/2014 00:00 |
  | A     | 20/06/2013 00:00 |
  | B     | 04/11/2014 00:00 |
  | C     | 20/06/2013 00:00 |
  +-------+------------------+
  </code></pre>
  
  <p>Desired result</p>
  
  <pre><code>+-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+
  | Mtype | 2015-03 | 2015-02 | 2015-01 | 2014-12 | 2014-11 | 2014-10 | 2014-9 | 2014-8 | 2014-7 |
  +-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+
  | A     |      45 |       7 |      54 |     875 |      45 |       7 |     54 |    875 |     25 |
  | B     |       7 |      78 |      78 |       7 |       7 |      78 |     78 |      7 |     78 |
  | C     |     546 |       6 |      87 |       5 |      45 |       6 |     87 |      5 |     25 |
  | D     |      54 |      78 |      54 |       8 |      54 |      78 |     54 |      7 |      7 |
  | E     |      78 |      78 |       7 |      45 |      78 |      78 |      7 |     45 |      6 |
  | F     |      46 |      54 |       6 |       8 |      46 |      54 |      6 |      8 |     75 |
  | G     |      54 |      87 |      87 |       7 |      54 |      87 |     87 |     45 |      8 |
  +-------+---------+---------+---------+---------+---------+---------+--------+--------+--------+
  </code></pre>
  
  <p>This is my take on the query but I can't figure out how to get the Pivot working</p>
  
  <pre><code>DECLARE @cols NVARCHAR (MAX)
  
  SELECT @cols = COALESCE (@cols + ',[' + LEFT(DATENAME(MONTH,join_date),3)+'/'+CAST(YEAR(join_date) AS
                 VARCHAR(4)) + ']', 
                '[' + LEFT(DATENAME(MONTH,join_date),3)+'/'+CAST(YEAR(join_date) AS VARCHAR(4)) + ']')
                 FROM    (SELECT DISTINCT join_date FROM MData T0) PV  
                 ORDER BY join_date
  
  DECLARE @query NVARCHAR(MAX)
  SET @query = 'SELECT * FROM 
               (
                   SELECT Mtype, LEFT(DATENAME(MONTH,join_date),3)+''/''+CAST(YEAR(join_date) AS join_date  VARCHAR(4)) FROM MData T0
               ) x
               PIVOT 
               (
                   count(join_date)
                   join_date IN (' + @cols + ')
              ) p;' 
  
  EXEC SP_EXECUTESQL @query
  </code></pre>