首页 > 代码库 > [MSSQL]採用pivot函数实现动态行转列

[MSSQL]採用pivot函数实现动态行转列

环境要求:2005+

在日常需求中常常会有行转列的事情需求处理。假设不是动态的行,那么我们能够採取case when 罗列处理。

在sql 2005曾经处理动态行或列的时候,通常採用拼接字符串的方法处理。在2005以后新增了pivot函数之后,我能够利用这样函数来处理。

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

--既然是用到了动态SQL,就有一个老话题:SQL注入。

建一个注入性字符的推断函数。 CREATE FUNCTION [dbo].[fn_CheckSQLInjection] ( @Col nvarchar(4000) ) RETURNS BIT --假设存在可能的注入字符返回true,反之返回false AS BEGIN DECLARE @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 @result END GO

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  #tempcob
from (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.解决方式:

--抓取数据写入暂时表#tempcob
select a.检验项目 , CONVERT(char(10),a.日期,120)日期,Convert(decimal(18,2),cast((SUM(a.測试数量)-SUM(a.不良数量)) as decimal(18,2))/sum(測试数量))*100  as 良率 
into  #tempcob
from (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 #tempcob
DECLARE @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.结果:

技术分享

[MSSQL]採用pivot函数实现动态行转列