欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-04-21 09:42:14
...

环境要求:2005+ 在日常需求中经常会有行转列的事情需求处理,如果不是动态的行,那么我们可以采取case when 罗列处理。 在sql 2005以前处理动态行或列的时候,通常采用拼接字符串的方法处理,在2005以后新增了pivot函数之后,我可以利用这样函数来处理。 1.

环境要求: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)

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)


4.结果:

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