Solution string cannot exceed 8000 methods and cross tabulation Processing

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 = ''