[MSSQL]採用pivot函數實現動態行轉列，mssqlpivot

[MSSQL]採用pivot函數實現動態行轉列，mssqlpivot

環境要求：2005+

在日常需求中經常會有行轉列的事情需求處理，如果不是動態行，那麼我們可以採取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.結果：

MSSQL裡的函數裡怎執行動態查詢 像 系統內建函式 Avg, Sum, Count 等

調用相關函數時，系統會先在所在的資料庫尋找，如果沒有再去本機伺服器進行查詢；一般我們新的資料庫都是以系統模版來建立的，系統中的資料庫內建的有許多函數，因此由此繼承而來，在資料庫查詢中只要調用方式，也就是格式合法，SQL編譯環境就會進行動態查詢！
 
mssql的函數中怎在執行動態查詢語句

mssql函數內部只能使用簡單的sql語句,邏輯控制語句，複雜一點的預存程序是不能調用的，也不能使用execute sp_executesql 或者execute。

換成預存程序吧。