Drop Table iClaimsYrPivot

DECLARE @pivot_cols NVARCHAR(MAX); 
SELECT @pivot_cols = 
STUFF((SELECT ',MAX(CASE WHEN year_month = ''' + year_month + 
               ''' THEN perc END) AS [' + year_month + ']' + 
               ',RANK() OVER(ORDER BY MAX(CASE WHEN year_month = ''' 

+ 
year_month + 
               ''' THEN perc END)) AS [' + year_month + ' rank]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt END) AS [' + year_month + ' icnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN tcnt END) AS [' + year_month + ' tcnt]' 
     FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9), 
                                 CAST(dowrdt AS DATETIME), 6), 6), 
                           CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 
126) 
           FROM iclaimsYear) AS T(year_month, sort) 
     ORDER BY sort 
     FOR XML PATH('') 
     ), 1, 1, ''); 

DECLARE @pivot_query NVARCHAR(MAX); 
SET @pivot_query = 
N'SELECT sort, reg,  ' + @pivot_cols + 
N' ,region, area, dist, doc, rpt ' + 
N' INTO    iClaimsYrPivot ' + 
N'FROM (SELECT sort, reg, region, area, dist, doc, rpt, icnt, tcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 
6), 
6) AS year_month, ' + 
                N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal
(6, 
1)) AS perc ' + 
         N'FROM iClaimsYear) AS F ' + 
N'GROUP BY sort, reg, area, dist, doc, rpt, region ' + 
N'ORDER BY sort;'; 


EXEC(@pivot_query); 

CAST(
         (  1.0 * icnt / NULLIF(tcnt, 0)  * 100
     ) AS decimal