It is often mentioned that when data is processed using the dynamic generation of SQL statements, the processing statements are too long and cannot be processed.
This issue is discussed below:
/* -- Data test environment --*/
If exists (select * From DBO. sysobjects where id = object_id (n' [TB] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [TB]
Go
Create Table Tb (unit name varchar (10), date datetime, sales INT)
Insert into TB
Select 'a unit ', '2017-01-01', 2001
Union all select 'unit B ', '1970-01-02', 2001
Union all select 'C unit ', '2017-01-03', 2001
Union all select 'd unit ', '2017-01-04', 2001
Union all select 'e unit ', '2017-01-05', 2001
Union all select 'f unit ', '2017-01-06', 2001
Union all select 'G units ', '2017-01-07', 2001
Union all select 'H unit ', '2017-01-08', 2001
Union all select 'I unit', '2017-01-09 ', 2001
Union all select 'J unit ', '2017-01-11', 2001
/* -- Required results
Date a Unit B Unit C Unit D unit E Unit F unit g unit H unit I Unit J Unit
----------------------------------------------------------
100 0 0 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
0 0 102 0 0 0 0 0 0 0 0 0 0
0 0 0 103 0 0 0 0 0 0 0 0
0 0 0 0 104 0 0 0 0 0 0
0 0 0 0 0 105 0 0 0 0
0 0 0 0 0 0 106 0 0 0
0 0 0 0 0 0 0 107 0 0
0 0 0 0 0 0 0 0 108 0
0 0 0 0 0 0 0 0 0 109
--*/
/* -- General Processing Method */
Declare @ SQL varchar (8000)
Set @ SQL = 'select date = convert (varchar (10), date, 120 )'
Select @ SQL = @ SQL + ', [' + unit name
+ '] = Sum (case unit name when ''' + unit name + '''then sales else 0 end )'
From (select distinct unit name from TB)
Exec (@ SQL + 'from TB group by convert (varchar (10), date, 120 )')
/* -- Problem: If the unit is large, the @ SQL value will be truncated, causing an error .*/
/* -- Three solutions are provided below :--*/
--/* -- Method 1. process multiple variables
-- Define variables. estimate how many variables are required to save all data.
Declare @ sql0 varchar (8000), @ sql1 varchar (8000)
--,... @ Sqln varchar (8000)
-- Generate a temporary data processing table
Select id = identity (INT, 0, 1), groupid = 0
, Value = ', [' + unit name + '] = sum (case unit name when '''
+ Organization name + '''then sales else 0 end )'
Into # temp from (select distinct unit name from TB)
-- Group a temporary table to determine the maximum number of bytes. A string of no more than 8000 can be combined. Assume five strings are used here.
Update # temp set groupid = ID/5 -- 5 indicates the number of units in each group.
-- Generate SQL statement processing string
-- Initialization
Select @ sql0 =''
, @ Sql1 =''
--...
--, @ Sqln
-- Get the processing string
Select @ sql0 = @ sql0 + value from # temp where groupid = 0 -- the first variable
Select @ sql1 = @ sql1 + value from # temp where groupid = 1 -- second variable
-- Select @ sqln = @ sqln + value from # temp where groupid = n -- nth variable
-- Query
Exec ('select date = convert (varchar (10), date, 120 )'
+ @ Sql0 + @ sql1
--... + @ Sqln
+ 'From TB group by convert (varchar (10), date, 120)
')
-- Delete a temporary table
Drop table # temp
/*
Advantage: relatively flexible. You only need to add a variable when the data volume is large. You do not need to change other parts.
Disadvantage: errors may occur if the data to be processed is estimated on its own.
*/
--*/
--/* -- Method 2. BCP + iSQL
-- This information is required because BCP + iSQL is used.
Declare @ servername varchar (250), @ username varchar (250), @ PWD varchar (250)
Select @ servername = 'zj' -- server name
, @ Username = ''-- User Name
, @ Pwd = ''-- Password
Declare @ tbname varchar (50), @ SQL varchar (8000)
-- Create a temporary data processing table
Set @ tbname = '[# temp _' + convert (varchar (40), newid () + ']'
Set @ SQL = 'create table mailto: % 20 + @ tbname + (value: varchar (8000 ))
Insert into mailto: % 20 + @ tbname + values (''create view'
+ Stuff (@ tbname, 2, 2, '') +'
Select date = convert (varchar (10), date, 120 )'')'
Exec (@ SQL)
Set @ SQL = 'insert into mailto: % 20 + @ tbname +
Select '', ['' + unit name + ''] = sum (case unit name when ''''''
+ Organization name + ''' then sales else 0 end )''
From (select distinct unit name from TB)'
Exec (@ SQL)
Set @ SQL = 'insert into mailto: % 20 + @ tbname +
Values (''from TB group by convert (varchar (10), date, 120 )'')'
Exec (@ SQL)
-- Generate the file for creating the view. Note that the file c: \ temp.txt is used.
Set @ SQL = 'bcp "mailto: % 20 + @ tbname +" out "C: \ temp.txt"/s "'
Mailto: + @ servername + "/u" mailto: % 20 + @ username + "/P" mailto: % 20 + @ PWD + "/C'
Exec master .. xp_mongoshell @ SQL
-- Delete a temporary table
Set @ SQL = 'drop table mailto: % 20 + @ tbname
Exec (@ SQL)
-- Call iSQL to generate a data processing view
Set @ tbname = stuff (@ tbname, 2, 2 ,'')
Set @ SQL = 'isql/s "mailto: % 20 + @ servername
+ Case @ username when ''then'' "/e 'else'"/u "mailto: % 20 + @ username +"/P "mailto: % 20 + @ PWD + % 20% 22 end
+ '/D "' + db_name () + '"/I "C: \ temp.txt "'
Exec master .. xp_mongoshell @ SQL
-- Call the view to display the processing result
Set @ SQL = 'select * From mailto: % 20 + @ tbname +
Drop view mailto: % 20 + @ tbname
Exec (@ SQL)
/*
Advantages:ProgramAutomatic Processing, no judgment error
Disadvantages: complicated, with many steps and error-prone, and requires certain operator Permissions
*/
--*/
--/* -- Method 3. processing of multiple variables, combining the advantages of method 1 and method 2, solves the problem of human judgment in method 1 and automatically defines variables based on the data volume to be processed, at the same time, the complexity of method 2 is avoided.
Declare @ sqlhead varchar (8000), @ sqlend varchar (8000)
, @ Sql1 varchar (8000), @ sql2 varchar (8000), @ sql3 varchar (8000), @ sql4 varchar (8000)
, @ I int, @ IC varchar (20)
-- Generate a temporary data processing table
Select id = identity (INT, 0, 1), gid = 0
, A = ', [' + unit name + '] = sum (case unit name when '''
+ Organization name + '''then sales else 0 end )'
Into # From (select distinct unit name from TB)
-- Determine how many variables are needed for processing
Select @ I = max (LEN (A) from #
Print @ I
Set @ I = 7800/@ I
-- Group temporary table
Update # Set gid = ID/@ I
Select @ I = max (GID) from #
-- Generate data processing statements
Select @ sqlhead = ''' select date = convert (varchar (10), date, 120 )'''
, @ Sqlend = ''' from TB group by convert (varchar (10), date, 120 )'''
, @ Sql1 = '', @ sql2 = 'select', @ sql3 ='', @ sql4 =''
While @ I> = 0
Select @ Ic = cast (@ I as varchar), @ I = @ I-1
, @ Sql1 = '@' + @ IC + 'varchar (8000), '+ @ sql1
, @ Sql2 = @ sql2 + '@' + @ IC + '= '''','
, @ Sql3 = 'select @ '+ @ IC +' = @ '+ @ IC +' + A from # Where mailto: gid = % 20 + @ IC
+ Char (13) + @ sql3
, @ Sql4 = @ sql4 + ', @' + @ IC
Select @ sql1 = 'desc' + Left (@ sql1, Len (@ sql1)-1) + char (13)
, @ Sql2 = left (@ sql2, Len (@ sql2)-1) + char (13)
, @ Sql3 = left (@ sql3, Len (@ sql3)-1)
, @ Sql4 = substring (@ sql4, 2,8000)
-- Execute
Exec (@ sql1 + @ sql2 + @ sql3 +'
Exec (mailto: % 20 + @ sqlhead + % 20 + % 20 + @ sql4 + % 20 + % 20 + @ sqlend + % 20)
)
-- Delete a temporary table
Drop table #
--*/
In method 3, the key to modification is the following two sentences. Other changes are basically not required:
-- Generate a temporary data processing table and modify the content after a = to the corresponding processing statement
Select id = identity (INT, 0, 1), gid = 0
, A = ', [' + code + '] = sum (Case B. c_code when '''
+ Code + ''' then B. Value else 0 end )'
Into # From # class
-- generate a data processing statement and assign @ sqlhead and @ sqlend to the corresponding processing statement header and tail.
select @ sqlhead = ''' select. ID,. name,. code '''
, @ sqlend = ''' from # depart a, # value B where. code = B. d_code group by. ID,. code,. name '''
, @ sql1 = '', @ sql2 = 'select', @ sql3 ='', @ sql4 = ''