Method of resolving string not exceeding 8000 and handling of cross table

It is often mentioned that when processing data with the method of dynamically generating SQL statements, the processing of the statement is too long to deal with the problem
The following is a discussion of this issue:

/*--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 ', ' 2001-01-01 ', 100
UNION ALL SELECT ' B unit ', ' 2001-01-02 ', 101
UNION ALL SELECT ' C unit ', ' 2001-01-03 ', 102
UNION ALL SELECT ' d unit ', ' 2001-01-04 ', 103
UNION ALL SELECT ' E units ', ' 2001-01-05 ', 104
UNION ALL SELECT ' F unit ', ' 2001-01-06 ', 105
UNION ALL SELECT ' G Unit ', ' 2001-01-07 ', 106
UNION ALL SELECT ' H unit ', ' 2001-01-08 ', 107
UNION ALL SELECT ' I unit ', ' 2001-01-09 ', 108
UNION ALL SELECT ' J unit ', ' 2001-01-11 ', 109

/*--Requirements Results
Date a Unit B unit C Unit D unit E Unit F Unit G Unit H Unit I unit J Unit
---------- ----- ----- ----- ----- ----- ----- ----  ----  ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*/

/*--Conventional 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) a
EXEC (@sql + ' from TB GROUP by CONVERT (varchar (10), date, 120) ')

/*--problem: If a lot of units, at this time, the value of @SQL will be truncated, resulting in an error. * *

/*--Here are three ways to solve this problem:--*/

--/*--Method 1. Multiple variable processing

--Define variables to estimate how many variables are needed to save all data
DECLARE @sql0 varchar (8000), @sql1 varchar (8000)
--,... @sqln varchar (8000)

--Generate a Data processing temp table
Select Id=identity (int,0,1), groupid=0
, value = ', [' + Unit name + ']=sum ' (case unit name when '
+ Unit name + ' then sales else 0 end '
Into #temp from (select distinct unit name from TB) a

--Group temporary tables, to determine how many units can be grouped into a string of not more than 8000, where the assumption is 5
Update #temp Set GROUPID=ID/5--5 number of units per group

--Generate SQL statement processing strings
--Initialization
Select @sql0 = '
, @sql1 = '
-- ...
--, @sqln

--Get the processing string
Select @sql0 = @sql0 + value from #temp where Groupid=0--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 temporary tables
drop table #temp

/*
Advantages: More flexible, the data volume when only need to add variables on the line. Don't change the other parts.
Disadvantage: If you want to estimate the processing data, you will get an error.
*/
--*/

--/*--Method 2. Bcp+isql

This information is needed because of the need to use Bcp+isql
DECLARE @servername varchar (), @username varchar (), @pwd varchar (250)
Select @servername = ' ZJ '-server name
, @username = '--User name
, @pwd = '--Password

DECLARE @tbname varchar (@sql), varchar (8000)

--Create a data processing temp table
Set @tbname = ' [# #temp_ ' +convert (varchar), newid ()] + '] '
Set @sql = ' CREATE table ' + @tbname + ' (value varchar (8000))
Insert INTO ' + @tbname + ' values (' Create VIEW '
+stuff (@tbname, 2,2, ' ") + ' as
Select Date =convert (varchar (10), date, 120) ') '
EXEC (@sql)

Set @sql = ' INSERT INTO ' + @tbname + '
Select ', [' + Unit name + ']=sum (case unit name when '] '
+ Unit name + ' ' then sales else 0 end '
From (select DISTINCT unit name from TB) a '
EXEC (@sql)

Set @sql = ' INSERT INTO ' + @tbname + '
VALUES (' From TB Group by CONVERT (varchar (10), date, 120) ') '
EXEC (@sql)

--Generate a file to create a view, note using the file: C:/temp.txt
Set @sql = ' bcp ' + @tbname + ' out ' c:/temp.txt '/S '
+ @servername + ' "/u" ' + @username + ' "/P" ' + @pwd + ' "/C '
EXEC master.. xp_cmdshell @sql

--Delete temporary tables
Set @sql = ' drop table ' + @tbname
EXEC (@sql)

--Call isql to generate a data processing view
Set @tbname =stuff (@tbname, 2, 2, "")
Set @sql = ' isql/s ' + @servername
+case @username when ' then ' "/e ' Else '"/u "' + @username + '"/P "' + @pwd + '" ' End
+ '/d ' +db_name () + ' "/I" C:/temp.txt ""

EXEC master.. xp_cmdshell @sql

--Calls the view, displays the processing result
Set @sql = ' select * from ' + @tbname + '
Drop View ' + @tbname
EXEC (@sql)

/*
Advantage: The procedure is processed automatically, there is no problem of judging wrong
Disadvantages: Complex, through many steps, error prone, and require certain operator permissions
*/
--*/

--/*--Method 3. Multiple variable processing, combining the advantages of Method 1 and Method 2, solves the problem of human judgment in Method 1, and automatically defines the variables according to the amount of data to be processed, and avoids the tedious of method 2.

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 Data processing temp table
Select Id=identity (int,0,1), gid=0
, A= ', [' + Unit name + ']=sum ' (case unit name when '
+ Unit name + ' then sales else 0 end '
Into # from (select DISTINCT unit name from TB) a

--Determine how many variables are needed to handle
Select @i=max (Len (a)) from #
Print @i
Set @i=7800/@i

--Grouping temporary tables
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 gid= ' + @ic
+char + @sql3
, @sql4 = @sql4 + ', @ ' + @ic

Select @sql1 = ' Declare ' +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)

-Implementation
EXEC (@sql1 + @sql2 + @sql3 + '
EXEC (' + @sqlhead + ' + ' + @sql4 + ' + ' + @sqlend + ') '
)

--Delete temporary tables
DROP TABLE #
--*/

In Method 3, the key to be modified is the following two sentences, others are not used for change basically:

--Generate a data processing temporary table, modify the contents of the a= after 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 data processing statements, @sqlhead, @sqlend assigned to the corresponding processing statement head and tail
Select @sqlhead = "' Select A.id,a.name,a.code '"
, @sqlend = ' from #Depart A, #Value b where A.code=b.d_code group by A.id,a.code,a.name '
, @sql1 = ', @sql2 = ' select ', @sql3 = ', @sql4 = '