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);
 
执行结果如下:
sql动态列查询

 

 

sql动态列查询

上一篇:【Unity】计时器


下一篇:Oracle OCP 19c 认证1Z0-083考试题库(第3题)