sql server 动态行专列 sql动态列查询

最近开发是遇到sql查询时，以查询结果作为列名，也就是动态列查询，案列如下：

 

declare @sql varchar(max);
set @sql='SELECT FName 销售经理,FNumber AS 客户代号 ,
SUM(CASE WHEN 月份 = ''01'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '01') + '],
SUM(CASE WHEN 月份 = ''02'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '02') +'],
SUM(CASE WHEN 月份 = ''03'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '03') +'],
SUM(CASE WHEN 月份 = ''04'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '04') +'],
SUM(CASE WHEN 月份 = ''05'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '05') +'],
SUM(CASE WHEN 月份 = ''06'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '06') +'],
SUM(CASE WHEN 月份 = ''07'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '07') +'],
SUM(CASE WHEN 月份 = ''08'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '08') +'],
SUM(CASE WHEN 月份 = ''09'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '09') +'],
SUM(CASE WHEN 月份 = ''10'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '10') +'],
SUM(CASE WHEN 月份 = ''11'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '11') +'],
SUM(CASE WHEN 月份 = ''12'' THEN (BordLocal) ELSE 0 END ) AS [' + (SELECT top 1 年月 from #OrderAmount WHERE 月份 = '12') +'],
(CASE WHEN SUM(BordLocal)<0 THEN 0 ELSE SUM(BordLocal) END ) AS 接单金额, (CASE WHEN SUM(BordLocal)<0 THEN 0 ELSE SUM(BordLocal) END )* 0.001 AS 奖金 FROM #OrderAmount t
GROUP BY FName,FNumber
ORDER BY FName,RIGHT(FNumber,2)'
 
exec(@sql);

 

执行结果如下：