Code Snippet:

 


DECLARE @COLUMNHEADERS VARCHAR(MAX);

SET @COLUMNHEADERS = STUFF( (SELECT DISTINCT ', [' + CAST(HIREDATE AS VARCHAR(255)) + ']'
               FROM EMPLOYEE
               FOR XML PATH(''), TYPE).VALUE('.', 'VARCHAR(MAX)')
              , 1, 2, ''); 

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =
  'SELECT * FROM
   (
     SELECT
       [DEPARTMENTID],
       [HIREDATE],
       [SORTNAME],
       ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN
     FROM EMPLOYEE 
   ) T
   PIVOT
   (
     MAX([SORTNAME]) FOR [HIREDATE] IN (' + @COLUMNHEADERS + ')
   ) P';

EXECUTE SP_EXECUTESQL @SQL;