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

Source: Internet
Author: User
Tags mssql sql injection prevention

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.