SQL Server Pivot：通过动态列分组

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

select @cols = STUFF((SELECT distinct ',' +  QUOTENAME(Replace(variable,char(CAST(0x0016 as int)),'')) valFROM TABLEDATAORDER BY val ascFOR XML PATH(''), TYPE          ).value('.', 'NVARCHAr(MAX)')   , 1, 1, '');SELECt @query ='SELECT  time,  country,  disease,  ' + @cols + 'FROM (    Select        time,        country,        disease,        variable,        convert(decimal(10, 2), value) as value    From        TABLEDATA    ) tPIVOT (  Sum(value)  FOR variable IN( ' + @cols + ' )' +' ) AS p; ';Execute(@query);

[Example Fiddle](http://www.sqlfiddle.com/#!3/81618f/59)