[MSSQL] adopts pivot function to implement dynamic row-to-column

Environmental requirements: 2005+

In everyday needs, there is often a need to deal with rows and columns. Assuming that it is not a dynamic line, we can take a case and then list the processing.

When SQL 2005 used to handle dynamic rows or columns, it was usually done with a concatenation string method. After I added the pivot function after 2005, I was able to use this function to handle it.

1. Dynamic SQL injection-inference function

--Since the use of dynamic SQL, there is an old topic: SQL injection.
An inferred function that constructs an injected character. CREATE FUNCTION [dbo]. [Fn_checksqlinjection] (@Col nvarchar (4000)) RETURNS bit--assuming that there is a possible injection character returning true, the inverse returns 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_% ') 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:

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

Select a. Test item, CONVERT (char (ten), a. Date, 120) date, convert (decimal (18,2), Cast ((SUM (A. Test quantity)-sum (A. Bad quantity)) as decimal (18,2))  /sum (test quantity)) *100 as  yield--into  #tempcobfrom (select date, inspection item, test quantity, bad quantity from process cob to test union ALL Select date, inspection item, test quantity,  Bad quantity from the Process Cob appearance UNION ALL              Select Date, test item, test quantity, bad quantity from process COB bound Test UNION ALL Select Test Date, ' fqc_cob_ inspection ', inspection quantity, 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. How to resolve:

--fetch data is written to the temporary table #tempcobselect A. Test item, CONVERT (char (120), A. Date, number of) date, convert (decimal (18,2), Cast ((SUM (A. Test quantity)-sum ( A. Bad quantity) as decimal (18,2))/sum (test quantity)) *100 as yield into #tempcobfrom (select date, inspection item, test quantity, bad quantity from process cob to test UNION ALL SEL ECT date, inspection item, test quantity, bad quantity from the Process Cob appearance UNION ALL select date, inspection item, test quantity, bad quantity from process cob bound to UNION ALL select test 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 item, a. Date--View temporary table data. Take distribution date (not repeated)--select date from #tempcob--select distinct date from #tempcobDECLARE @SQL NVARCHAR (4000) =n ";--this uses XML processing to process class words The 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 inference 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] adopts pivot function to implement dynamic row-to-column