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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://david.blog.csdn.net/article/details/40788859

环境要求: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.结果:



展开阅读全文

求助sql2005关于行转列 PIVOT的另一个用法

04-23

为方便各位洞察问题,提供测试代码如下:rn[code=SQL]rnCREATE TABLE salesByMonthrn(rnyear char(4),rnmonth char(3),rnamount money,rnPRIMARY KEY (year, month)rn)rnrnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Jan', 789.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Feb', 389.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Mar', 8867.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Apr', 778.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','May', 78.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Jun', 9.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Jul', 987.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Aug', 866.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Sep', 7787.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Oct', 85576.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Nov', 855.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2004','Dec', 5878.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2005','Jan', 7.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2005','Feb', 6868.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2005','Mar', 688.0000)rnINSERT INTO salesByMonth (year, month, amount)rnVALUES('2005','Apr', 9897.0000)rn--查看数据rnselect * from salesByMonthrn--按照常规行转列rn SELECT *rn FROM salesByMonth rnPIVOT ( SUM(amount) FOR month INrn ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])rn )trn显示结果rnyear Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Decrn---- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------rn2004 789.00 389.00 8867.00 778.00 78.00 9.00 987.00 866.00 7787.00 85576.00 855.00 5878.00rn2005 7.00 6868.00 688.00 9897.00 NULL NULL NULL NULL NULL NULL NULL NULLrnrn(2 行受影响)rnrnrn而我的需求是相反的操作,我要求把year作为列显示,呈现类似如下的结果:rn 2004 2005rnJan X X rnFeb rnMarrnAprrnMayrn[/code]rnrn求助各位达人! 论坛

没有更多推荐了,返回首页