Ajuda com consulta PIVOT

Isso é um pouco confuso para obter o resultado final porque você tem vários SCHEMA_VERpara cada data. Antes de demonstrar como fazer isso com SQL dinâmico, primeiro mostrarei como fazer isso com código estático para obter a lógica correta. Para obter o resultado final, você pode utilizar tanto o pivô quanto o não pivô.

Mas primeiro, eu mudaria sua consulta original para usar o seguinte:

select 
    row_number() over (
    partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
    ) as minrownum
, row_number() over (
    partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
    ) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build';

Consulte SQL Fiddle com demonstração . Eu costumava row_number()pegar o primeiro e o último SCHEMA_VERde cada data. Isso é necessário para que você possa concatenar apenas esses valores juntos para o comentário.

Em seguida, eu usaria uma tabela temporária para armazenar as linhas que possuem um minrownume maxrownumde 1. A tabela temporária conteria o upg_datee o comment. Esta coluna de comentário contém uma string concatenada do par de SCHEMA_VERpara cada data.

create table #srcData
(
    upg_date varchar(10),
    comment varchar(500)
);

O código para preencher a tabela temporária seria então:

;with cte as
(
  select 
        row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
        ) as minrownum
    , row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
        ) as maxrownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
  from audit_schema_version
  where SCHEMA_VER_REMARK like 'Stored procedure build'
)
insert into #srcData
select distinct
    c1.UPG_DATE,
    comment 
        = STUFF((
                  SELECT ' - ' + c2.SCHEMA_VER 
                  FROM cte c2
                  WHERE (c2.minrownum = 1 or c2.maxrownum = 1)
                    and c1.upg_date = c2.upg_date
                  order by c2.minrownum
                  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
from cte c1
where c1.minrownum = 1 or c1.maxrownum = 1;

Esta primeira passagem pelos seus dados permite:

|   upg_date |           comment |
|------------|-------------------|
| 03-23-2015 |            2.13.0 |
| 04-05-2013 |  1.6.13 - 1.16.13 |
| 04-17-2014 |   1.16.13 - 2.7.0 |
| 12-09-2014 |    2.8.0 - 2.12.0 |

Agora você ainda precisa obter uma contagem de cada data para o ano e o comentário concatenado completo. É aqui que o não pivô entra em jogo. Você pode usar o código a seguir para criar o comentário completo para cada ano e obter a contagem.

select distinct 
    Yr =  right(s1.upg_date, 4),
    cnt = count(*) over(partition by right(s1.upg_date, 4)),
    fullcomment 
            = STUFF((
                      SELECT '; ' + s2.comment 
                      FROM #srcData s2
                      WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
from #srcData s1;

Consulte SQL Fiddle com demonstração . Os dados agora se parecem com:

|   Yr | cnt |                       fullcomment |
|------|-----|-----------------------------------|
| 2013 |   1 |                  1.6.13 - 1.16.13 |
| 2014 |   2 |  1.16.13 - 2.7.0;  2.8.0 - 2.12.0 |
| 2015 |   1 |                            2.13.0 |

Como você pode ver, você tem várias colunas que precisam ser dinamizadas, para que você possa desarticular a coluna fullcommente cntem várias linhas. Isso pode ser feito usando a função UNPIVOT ou CROSS APPLY. Prefiro a aplicação cruzada aqui porque você deseja concatenar valores para criar os novos nomes de coluna:

;with cte as
(
    select distinct 
        Yr =  right(s1.upg_date, 4),
        cnt = count(*) over(partition by right(s1.upg_date, 4)),
        fullcomment 
                = STUFF((
                          SELECT '; ' + s2.comment 
                          FROM #srcData s2
                          WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                          FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
    from #srcData s1
) 
select [2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment]
from
(
    select c.col, val
    from cte d
    cross apply
    (
        values 
            (Yr, cast(cnt as nvarchar(50))),
            (Yr+'_comment', fullcomment)
    ) c (col, val)  
) d
pivot
(
    max(val)
    for col in ([2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment])
) piv;

Consulte SQL Fiddle com demonstração .

Depois de obter a lógica, você pode convertê-la facilmente em SQL dinâmico.

-- get list of the columns
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(col) 
                    from #srcData
                    cross apply
                    (
                        select right(upg_date, 4), right(upg_date, 4), 2 union all
                        select right(upg_date, 4), right(upg_date, 4)+'_comment', 1
                    ) c (yr, col, so)
                    group by yr, col, so
                    order by yr desc, so desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
    = 'SELECT ' + @cols + ' 
        from 
        (
            select c.col, val
            from
            (
                select distinct 
                    Yr =  right(s1.upg_date, 4),
                    cnt = count(*) over(partition by right(s1.upg_date, 4)),
                    fullcomment 
                            = STUFF((
                                      SELECT ''; '' + s2.comment 
                                      FROM #srcData s2
                                      WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                                      FOR XML PATH(''''), TYPE).value(''.[1]'', ''nvarchar(max)''), 1, 2, '''') 
                from #srcData s1
            ) d
            cross apply
            (
                values 
                    (Yr, cast(cnt as nvarchar(50))),
                    (Yr+''_comment'', fullcomment)
            ) c (col, val)  
        ) x
        pivot 
        (
           max(val)
           for col in (' + @cols + ')
        ) p '

exec sp_executesql @query;

Consulte SQL Fiddle com demonstração . Ambas as versões lhe darão o resultado:

| 2015 | 2015_comment | 2014 |                      2014_comment | 2013 |      2013_comment |
|------|--------------|------|-----------------------------------|------|-------------------|
|    1 |       2.13.0 |    2 |  1.16.13 - 2.7.0;  2.8.0 - 2.12.0 |    1 |  1.6.13 - 1.16.13 |

Adicionando explicação e um violino: http://sqlfiddle.com/#!6/c92b2/5 .

A consulta abaixo:
1. usa uma subconsulta para selecionar as versões min e max por data (min e max são aplicados a números inteiros para garantir que por instância 6 < 16)
2. Em seguida, seleciona o ano (para agrupar posteriormente), Data (para pedido) e as versões min - max

SELECT LEFT(UPG_DATE, 4) AS Year
    , UPG_DATE
    , CONVERT(varchar(1), MIN_VER/1000000) + '.' + CONVERT(varchar(2), (MIN_VER/1000 - (MIN_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MIN_VER%1000)
        + ' - ' + CONVERT(varchar(1), MAX_VER/1000000) + '.' + CONVERT(varchar(2), (MAX_VER/1000 - (MAX_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MAX_VER%1000) AS Versions
INTO #Versions
FROM (
    SELECT CONVERT(varchar(10), SCHEMA_VER_DATE, 112) as UPG_DATE
        , MIN(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MIN_VER
        , MAX(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MAX_VER
    FROM audit_schema_version
    WHERE SCHEMA_VER_REMARK like 'Stored procedure build'
    GROUP BY CONVERT(varchar(10), SCHEMA_VER_DATE, 112)
) Versions;

A seguir, como cada coluna será repetida (year e year_COMMENT), duas colunas são selecionadas para identificar os dados. As datas são contadas para saber o número de atualizações e as Versões são agrupadas por ano, enchendo para que fique tudo em uma linha. Isso nos dá a mesa final que será usada para pivotar.

SELECT Year, Year + '_COMMENT' as Year_COMMENT
    , COUNT(Year) AS Upgrades
    , STUFF((SELECT ' ; ' + SUB.Versions
                FROM #Versions SUB
                WHERE SUB.Year = V.Year
                ORDER BY UPG_DATE ASC
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(2000)')
            ,1,3,'') Versions
INTO #GroupedResults
FROM #Versions V
GROUP BY Year

SELECT * FROM #GroupedResults

Aqui estão os resultados:

| Year | Year_COMMENT | Upgrades | Versions                         |
|------|--------------|----------|----------------------------------|
| 2013 | 2013_COMMENT | 1        | 1.6.13 - 1.16.13                 |
| 2014 | 2014_COMMENT | 2        | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 |
| 2015 | 2015_COMMENT | 1        | 2.13.0 - 2.13.0                  |

Em seguida, uma variável é preenchida com as colunas, ordenadas como queremos exibi-las:

DECLARE @cols VARCHAR(1000),
    @finalQuery VARCHAR(2000)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(YEAR) + ',' + QUOTENAME(YEAR + '_COMMENT')
                    FROM #GroupedResults
                    GROUP BY YEAR
                    ORDER BY YEAR DESC
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(2000)')
    ,1,1,'')

Finalmente, a query abaixo usa cross apply, então obtemos:
1. A coluna col preenchida com os valores Year e Year_COMMENT
2. A coluna value preenchida com o número de atualizações, nas linhas correspondentes aos anos, e valores das versões, no linhas correspondentes a Year_COMMENTs
Um pivô é usado sobre as duas colunas resultantes, dando-nos os valores (número de atualizações alternando com versões) sobre a coluna (Anos alternando com Year_COMMENTs)

set @finalQuery = N'SELECT ' + @cols + N' from 
             (
                select col, value
                from #GroupedResults
                cross apply
                (
                    SELECT CAST(Upgrades AS VARCHAR(200)), Year
                    UNION ALL
                    SELECT CAST(Versions AS VARCHAR(200)), Year_COMMENT
                ) c (value, col)
            ) x
            pivot 
            (
                Min(value)
                for col in (' + @cols + N')
            ) p1
            ; '

EXEC (@finalQuery);

DROP TABLE #Versions;
DROP TABLE #GroupedResults;

Isso retorna os seguintes resultados:

| 2015 | 2015_COMMENT    | 2014 | 2014_COMMENT                     | 2013 | 2013_COMMENT     |
|------|-----------------|------|----------------------------------|------|------------------|
| 1    | 2.13.0 - 2.13.0 | 2    | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 | 1    | 1.6.13 - 1.16.13 |