[MSSQL]采用pivot函数实现动态行转列-Mysql Tutorial-php.cn

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

WBOY

Release： 2016-06-07 16:09:46

Original

2053 people have browsed it

环境要求：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&#39;%0x%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%;%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%&#39;&#39;%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%--%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%/*%*/%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%EXEC%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%xp_%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%sp_%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%SELECT%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%INSERT%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%UPDATE%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%DELETE%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%TRUNCATE%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%CREATE%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%ALTER%&#39;)
  OR UPPER(@Col) LIKE UPPER(N&#39;%DROP%&#39;)
  SET @result=1
 ELSE
  SET @result=0
 return @result
END
GO

Copy after login

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 送检日期,&#39;FQC_COB_检验&#39;,检验数量,不合格数量 from 制程FQC_COB_检验 
  ) as a 
  where CONVERT(char(10),日期,120)>=&#39;2014-10-01&#39;  and CONVERT(char(10),日期,120)<= &#39;2014-10-30&#39;
 group by a.检验项目,a.日期

Copy after login

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 送检日期,&#39;FQC_COB_检验&#39;,检验数量,不合格数量 from 制程FQC_COB_检验 
  ) as a 
  where CONVERT(char(10),日期,120)>=&#39;2014-10-01&#39;  and CONVERT(char(10),日期,120)<= &#39;2014-10-30&#39;
 group by a.检验项目,a.日期 
 
--查看临时表数据，取分布日期（不重复）
--select   日期 from #tempcob
--select  distinct 日期 from #tempcob
DECLARE @SQL NVARCHAR(4000)=N&#39;&#39;;
--这里使用了xml处理来处理类组字符串
SET @SQL=STUFF((SELECT N&#39;,&#39;+QUOTENAME(b.日期) FROM (select  distinct 日期 from #tempcob) as b
 FOR XML PATH(&#39;&#39;)),1,1,N&#39;&#39;); 
--加入了xml处理和SQL注入预防判断
IF  dbo.fn_CheckSQLInjection(@SQL)=0 
SET @SQL=&#39;select * from #tempcob pivot (max(良率) for 日期 in (&#39;+@SQL+&#39;)) as tt&#39;
EXEC(@SQL);
drop table #tempcob

Copy after login

4.结果：