SQL Server采用pivot函数实现动态行转列

当前位置：点晴教程→知识管理交流 →『技术文档交流』

admin

2026年4月15日 15:23 本文热度 54

在日常需求中经常会有行转列的事情需求处理，如果不是动态的行，那么我们可以采取case when 罗列处理。

在sql 2005以前处理动态行或列的时候，通常采用拼接字符串的方法处理，在2005以后新增了pivot函数之后，我可以利用这样函数来处理。

1.动态SQL注入式判断函数

--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。CREATE FUNCTION [dbo].[fn_CheckSQLInjection]( @Col nvarchar(4000))RETURNS BIT --如果存在可能的注入字符返回true,反之返回falseASBEGINDECLARE @result bit;  IF       UPPER(@Col) LIKE UPPER(N'%0x%')  OR UPPER(@Col) LIKE UPPER(N'%;%')  OR UPPER(@Col) LIKE UPPER(N'%''%')  OR UPPER(@Col) LIKE UPPER(N'%--%')  OR UPPER(@Col) LIKE UPPER(N'%/*%*/%')  OR UPPER(@Col) LIKE UPPER(N'%EXEC%')  OR UPPER(@Col) LIKE UPPER(N'%xp_%')  OR UPPER(@Col) LIKE UPPER(N'%sp_%')  OR UPPER(@Col) LIKE UPPER(N'%SELECT%')  OR UPPER(@Col) LIKE UPPER(N'%INSERT%')  OR UPPER(@Col) LIKE UPPER(N'%UPDATE%')  OR UPPER(@Col) LIKE UPPER(N'%DELETE%')  OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%')  OR UPPER(@Col) LIKE UPPER(N'%CREATE%')  OR UPPER(@Col) LIKE UPPER(N'%ALTER%')  OR UPPER(@Col) LIKE UPPER(N'%DROP%')  SET @result=1 ELSE  SET @result=0 return @resultENDGO

2.需求：

--通过日期查询几个表联合，按照检验项目分类，按日期横向展示

select a.检验项目 , CONVERT(char(10),a.日期,120)日期,Convert(decimal(18,2),cast((SUM(a.测试数量)-SUM(a.不良数量)) as decimal(18,2))/sum(测试数量))*100  as 良率 --into  #tempcobfrom (select 日期,检验项目, 测试数量, 不良数量 from 制程COB成测 union all select 日期,检验项目, 测试数量, 不良数量 from 制程COB外观 union all              select 日期,检验项目,测试数量, 不良数量 from 制程COB绑测 union all select 送检日期,'FQC_COB_检验',检验数量,不合格数量 from 制程FQC_COB_检验   ) as a   where CONVERT(char(10),日期,120)>='2014-10-01'  and CONVERT(char(10),日期,120)<= '2014-10-30' group by a.检验项目,a.日期

3.解决方案：

--抓取数据写入临时表#tempcobselect a.检验项目 , CONVERT(char(10),a.日期,120)日期,Convert(decimal(18,2),cast((SUM(a.测试数量)-SUM(a.不良数量)) as decimal(18,2))/sum(测试数量))*100  as 良率 into  #tempcobfrom (select 日期,检验项目, 测试数量, 不良数量 from 制程COB成测 union all select 日期,检验项目, 测试数量, 不良数量 from 制程COB外观 union all              select 日期,检验项目,测试数量, 不良数量 from 制程COB绑测 union all select 送检日期,'FQC_COB_检验',检验数量,不合格数量 from 制程FQC_COB_检验   ) as a   where CONVERT(char(10),日期,120)>='2014-10-01'  and CONVERT(char(10),日期,120)<= '2014-10-30' group by a.检验项目,a.日期 
--查看临时表数据，取分布日期（不重复）--select   日期 from #tempcob--select  distinct 日期 from #tempcobDECLARE @SQL NVARCHAR(4000)=N'';--这里使用了xml处理来处理类组字符串SET @SQL=STUFF((SELECT N','+QUOTENAME(b.日期) FROM (select  distinct 日期 from #tempcob) as b FOR XML PATH('')),1,1,N''); --加入了xml处理和SQL注入预防判断IF  dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL='select * from #tempcob pivot (max(良率) for 日期 in ('+@SQL+')) as tt'EXEC(@SQL);drop table #tempcob

4.结果：

阅读原文：原文链接

该文章在 2026/4/15 18:04:03 编辑过

关键字查询

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。