SQL Query to make dynamic columns

You can use the PIVOT table operator instead, something like this:

SELECT *
FROM
(
  SELECT
    DATENAME(MONTH,DateOpened) as Month,
    DATENAME(Year,DateOpened) AS Year,
    DateOpened
  FROM Incidents
) AS t
PIVOT
(
  COUNT(DateOpened)
  FOR Year IN([2013], [2014], [2015])
) AS p;

• SQL Fiddle Demo

If you don't need to write the list of years and do it dynamically for any year, you have to use dynamic SQL to run the query dynamically, like this:

DECLARE @cols AS NVARCHAR(MAX);    
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(DATENAME(Year,DateOpened))
                      from Incidents AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'SELECT * , '+ @cols + '
FROM
(
  select 
    DATENAME(MONTH,DateOpened) as Month,
    DATENAME(Year,DateOpened) AS Year,
    DateOpened
  FROM Incidents
) AS t
PIVOT
(
  COUNT(DateOpened)
  FOR Year IN(' + @cols + ')' +
                  ') p';

  execute(@query);

• SQL Fiddle Demo

This will give you something like this:

|     Month | 2014 | 2015 | 2014 | 2015 |
|-----------|------|------|------|------|
|     April |    0 |    3 |    0 |    3 |
|    August |    1 |    1 |    1 |    1 |
|  December |    1 |    0 |    1 |    0 |
|  February |    0 |    1 |    0 |    1 |
|      July |    1 |    1 |    1 |    1 |
| September |    1 |    0 |    1 |    0 |

Note that: Both the queries won't list any month that has no dates on the original table. If you want to list any month that is not listed on the table with counts 0, you have to modify the anchor query so that it lists all the months even if not listed:

  SELECT
    m.Name as Month,
    i.Year,
    i.DateOpened 
  FROM
  (
   VALUES ('Janurary'), ('February'), ('March'),
          ('April'),    ('May'),      ('June'),
          ('July'),     ('August'),   ('September'),
          ('October'),  ('November'), ('December')
   ) AS m(Name) 
  LEFT JOIN
  (
    SELECT 
      DATENAME(MONTH,DateOpened) as Month,
      DATENAME(Year,DateOpened) AS Year,
      DateOpened 
    FROM Incidents 
  ) AS i ON i.Month = m.Name

and replace it in the dynamic query.

• Updated SQL Fiddle

This will give you the missing months with zeros results:

|     Month | 2014 | 2015 | 2014 | 2015 |
|-----------|------|------|------|------|
|  Janurary |    0 |    0 |    0 |    0 | <<
|  February |    0 |    1 |    0 |    1 |
|     March |    0 |    0 |    0 |    0 | <<
|     April |    0 |    3 |    0 |    3 |
|       May |    0 |    0 |    0 |    0 | <<
|      June |    0 |    0 |    0 |    0 | <<
|      July |    1 |    1 |    1 |    1 |
|    August |    1 |    1 |    1 |    1 |
| September |    1 |    0 |    1 |    0 |
|   October |    0 |    0 |    0 |    0 | <<
|  November |    0 |    0 |    0 |    0 | <<
|  December |    1 |    0 |    1 |    0 |

A simple create table something like this..

  CREATE TABLE #tmpIncidents
  (
    IncidentName NVARCHAR(50)
    , DateOpened DATETIME
  )

  INSERT INTO #tmpIncidents (IncidentName,DateOpened) VALUES 
  ('Test1',GETDATE()),
  ('Test1',DATEADD(YEAR,-1,GETDATE())),
  ('Test1',DATEADD(YEAR,-2,GETDATE())),
  ('Test1',DATEADD(YEAR,2,GETDATE())),
  ('Test1',DATEADD(YEAR,1,GETDATE())),
  ('Test1',DATEADD(YEAR,3,GETDATE()))

Then a build up query for multiple years

  DECLARE @columnVar NVARCHAR(4000)

  SELECT @columnVar =
      (SELECT DISTINCT
            '[' + CONVERT(NVARCHAR(150),DATEPART(YEAR,DateOpened)) + '],' AS [text()]
      FROM #tmpIncidents
      FOR XML PATH('')
      )
  SET @columnVar = (SELECT LEFT(@columnVar,LEN(@columnVar)-1))
  --SELECT @columnVar --so you can see how it looks..

Then execute your query.

  EXEC ('
  SELECT
    pv.*
  FROM 
  (
    SELECT DATENAME(MONTH,DateOpened) AS [DateName], DATEPART(YEAR,DateOpened) AS [YEAR], IncidentName FROM #tmpIncidents
  ) src
  PIVOT
  (
    COUNT(IncidentName)
    FOR [YEAR] IN (' + @columnVar + ')
  ) pv;
  ')