[MSSQL] use the convert function to implement dynamic row-to-column conversion.

[MSSQL] use the convert function to implement dynamic row-to-column conversion.

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:

How to execute dynamic queries in functions in MSSQL, such as Avg, Sum, and Count FUNCTIONS IN THE SYSTEM

When you call related functions, the system first searches for them in the database where they are located. If they are not found on the local server, new databases are generally created using system templates, the database in the system comes with many functions, so it inherits from this. In database queries, as long as the calling method, that is, the format is legal, the SQL compilation environment will perform dynamic queries!
 
How to execute dynamic query statements in mssql Functions

Mssql functions can only use simple SQL statements and logical control statements. A complex stored procedure cannot be called, or execute sp_executesql or execute.

Change to a stored procedure.