[MSSQL] using pivot function to implement dynamic row to column

Environmental requirements: 2005+

In daily needs, there are often rows of things to do, if not dynamic rows, then we can take the case when the list of processing.

When dealing with dynamic rows or columns before SQL 2005, it is usually done by stitching strings, and after 2005 new pivot functions have been added, I can use this function to handle.

1. Dynamic SQL injection-type judgment function

--Since the use of dynamic SQL, there is an old topic: SQL injection. Build a judgment function for the injected character. CREATE FUNCTION [dbo]. [Fn_checksqlinjection] (@Col nvarchar (4000)) RETURNS bit-Returns true if there is a possible injection character, whereas the Falseasbegindeclare @result bit is returned;  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_% ') C8/>or UPPER (@Col) like UPPER (n '%sp_% ')  or UPPER (@Col) like UPPER (n '%select% ')  or UPPER (@Col) like UPPER (n '%I nsert% ')  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. Requirements:

--Through the date query several table union, according to the inspection item classification, by the date horizontal display

Select a. Test item, CONVERT (char (120), A. Date, time (+), convert (Decimal (18,2), cast (SUM (number of tests)-sum (a. Bad quantity)) as decimal (18,2))  /sum (number of tests)) *100 as  yield--into  #tempcobfrom (select Date, test item, quantity tested, bad quantity from process cob to test union ALL Select date, inspection item, number of tests,  Bad quantity from the Process Cob appearance UNION ALL              Select Date, inspection item, test quantity, bad quantity from process COB binding Test UNION ALL Select Test Date, ' fqc_cob_ inspection ', quantity tested, unqualified quantity from Process Fqc_cob_ Inspection   )   as a where CONVERT (char (10), date, +) >= ' 2014-10-01 ' and  CONVERT (char (10), date, +) <= ' 2014-10-30 ' GROUP by a. Inspection project, a. Date

3. Solution:

--fetch data to the temporary table #tempcobselect A. Test item, CONVERT (char (120), date of a.) date, convert (decimal (18,2), cast (SUM (number of tests)-sum ( A. Bad quantity) as decimal (18,2))/sum (test quantity)) *100 as yield into #tempcobfrom (select Date, test item, number of tests, bad quantity from process cob to testing UNION ALL SEL ECT date, inspection item, test quantity, bad quantity from process COB appearance UNION ALL select date, inspection item, test quantity, bad quantity from process COB bound Test UNION ALL select Date, ' Fqc_cob_ inspection ', number of tests, number of unqualified from process fqc_cob_ inspection] as a where CONVERT (char (10), date, +) >= ' 2014-10-01 ' and CONVERT (char (10), Date, <= ' 2014-10-30 ' GROUP by a. Inspection project, a. Date--View temporary table data, take distribution date (not duplicated)--select date from #tempcob--select distinct date from  #tempcobDECLARE @SQL NVARCHAR (4000) =n ";--this uses XML processing to handle class group string set @SQL =stuff ((select N ', ' +quotename (b. Date) from (select Distinct date from #tempcob) as B for XML PATH (")), 1,1,n"); --Added XML processing and SQL injection prevention judgment if dbo.fn_checksqlinjection (@SQL) =0 SET @SQL = ' select * from #tempcob pivot (max (yield) for date in (' [Emai L protected]+ ') as TT ' EXEC (@SQL);d ROP table #tempcob

4. Results:

[MSSQL] using pivot function to implement dynamic row to column