[MSSQL] dynamic row-to-column conversion using the lift Function

Environment requirements: more than 2005 of daily requirements often involve the handling of Row-to-Column Operations. If it is not a dynamic row, we can use casewhen for listing. When dealing with dynamic rows or columns before sql2005, we usually use the concatenation string method. After MySQL 2005 and later, after the nested function is added, I can use this function for processing. 1.

Environment requirements: more than 2005 of daily requirements often involve the need to handle row-to-Column Operations. If it is not a dynamic row, we can use case when to list and process it. When dealing with dynamic rows or columns before SQL 2005, we usually use the concatenation string method. After SQL 2005 and later, after the nested function is added, I can use this function for processing. 1.

Environment requirements: 2005 +

In daily requirements, we often need to handle row-to-column operations. If it is not a dynamic row, we can use case when listing.

When dealing with dynamic rows or columns before SQL 2005, we usually use the concatenation string method. After SQL 2005 and later, after the nested function is added, I can use this function for processing.

1. dynamic SQL Injection judgment Function

-- Since dynamic SQL is used, there is an old topic: SQL Injection. Create a judgment function for injecting characters. Create function [dbo]. [fn_CheckSQLInjection] (@ Col nvarchar (4000) returns bit -- RETURNS true IF possible injection characters exist, and 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. Requirements:

-- Query the Union of several tables by date, classify the tables by test item, and display them horizontally by date

Select. check item, CONVERT (char (10),. date, 120) date, Convert (decimal (), cast (SUM (. test quantity)-SUM (. negative Quantity) as decimal (100)/sum (test quantity) * as yield -- into # tempcobfrom (select date, test item, test quantity, bad quantity from COB into test union all select date, test item, test quantity, bad quantity from process COB appearance union all select date, test item, test quantity, bad quantity from COB binding test union all select inspection date, 'fqc _ COB _ check', inspection quantity, unqualified quantity from FQC_COB _ Inspection) as a where CONVERT (char (10), date, 120)> = '2017-10-01 'and CONVERT (char (10), date, 2014) <= '2014-10-30 'group by. inspection item,. date

3. solution:

-- Capture data and write it to the temporary table # tempcobselect. check item, CONVERT (char (10),. date, 120) date, Convert (decimal (), cast (SUM (. test quantity)-SUM (. negative Quantity) as decimal (100)/sum (test quantity) * as yield into # tempcobfrom (select date, test item, test quantity, bad quantity from COB into test union all select date, test item, test quantity, bad quantity from process COB appearance union all select date, test item, test quantity, bad quantity from COB binding test union all select inspection date, 'fqc _ COB _ check', inspection quantity, unqualified quantity from FQC_COB _ Inspection) as a where CONVERT (char (10), date, 120)> = '2017-10-01 'and CONVERT (char (10), date, 2014) <= '2014-10-30 'group by. inspection item,. date -- view temporary table data, take the distribution date (not repeated) -- select date from # tempcob -- select distinct date from # tempcobDECLARE @ SQL NVARCHAR (4000) = n ''; -- xml processing is used to process the class group string SET @ SQL = STUFF (SELECT n', '+ QUOTENAME (B. date) FROM (select distinct date from # tempcob) as B FOR XML PATH (''), n ''); -- added xml processing and SQL injection to prevent IF dbo judgment. fn_CheckSQLInjection (@ SQL) = 0 SET @ SQL = 'select * from # tempcob partition (max (yield) for date in ('+ @ SQL + ')) as tt 'exec (@ SQL); drop table # tempcob

4. Results: