Pivot Large Number of Columns to go over 4096 Limit

An interesting question about why a piece of code is working over 
the limit of 4096 for columns in a SELECT list.
I didn't find a reason but come up with the following code to pivot large number of columns
and save the result to an Excel file manually.



  
 



;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

select n 
into numbers
from nums
where n<=5000

DECLARE @cols AS NVARCHAR(max)=N'' , 
@sql AS NVARCHAR(max)=N''
 
select @cols= STUFF((select  ', '+ quotename(n ,']')
FROM numbers
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
/*

--Non working

Set @sql=N'SELECT '+ @cols +' FROM (
Select  n   FROM numbers) src
PIVOT (Max(n) for n IN ('+ @cols +')) pvt'
*/
/*
Msg 1056, Level 15, State 1, Line 1
The number of elements in the select list 
exceeds the maximum allowed number of 4096 elements.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'src'.

*/
 
 --Use *  instead of the column list
 Set @sql=N'SELECT * FROM (
Select  n   FROM numbers) src
PIVOT (Max(n) for n IN ('+ @cols +')) pvt'
exec sp_executesql @sql;

--

drop table numbers

  

Copy the result with header and save it to an Excel worksheet.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dcd20ca2-419a-46ac-ba6d-1bf91219feb6/select-maxcolumns-4096-gt-then-why-does-this-snippet-work?forum=transactsql


Last Server Started Time

 Powershell:

Get-CimInstance -ClassName win32_operatingsystem | select csname, lastbootuptime

https://serverfault.com/questions/159612/windows-server-last-reboot-time


msdb.dbo.sp_send_dbmail : Query Results Seperator Setting —@query_result_separator How to remove space between columns.


Solution:  @query_result_separator =0x00


---Sample for test

create table test (
 ID varchar(50), col1 varchar(50)
 ,Cube varchar(50),col2 varchar(50),col3 varchar(50))
 insert into test values('aa','123','dd1','Pieces','www1')
,('aa2','223','dd2','Pieces2','www2')
,('aa3','323','dd3','Pieces3','www3')
,('aa4','423','dd4','Pieces4','www4')

Declare @Qry nvarchar(max)
Set @Qry = ' Select ID,ID,'+'''    ''' +'as EmptyFill1,col1,col2,col3 from test'

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'myprofilename',
 @recipients = 'mygmailaccount@gamil.com',
 @query = @Qry,
 --@query_result_separator =' ', --default
 --@query_result_separator =',',
 @query_result_separator =0x00, -- no space between columns!!!***************
-- @query_result_separator =0x09, --tab
 -- @query_result_separator =0x0B, --vertical tab

 @subject = 'Subject here',
 @body = 'This is body',
 @query_result_header = 1,
@query_no_truncate = 1,
@query_result_no_padding = 0 

 drop table  test

  

You can also define a variable to hold delimiter:

declare @separator char(1)=char(9)–tab or 0x09 or a literal tab inside single quotes.

Or
—*************This is not documented in any other place************
declare @separator char(1) =char(0) — or 0x00 –no space

….
@query_result_separator =@separator

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/99483c5f-8be4-4c92-a656-2c7b0086f236/query-results-seperator?forum=transactsql


Remove Leading Zeros in a Varchar Column but keep inside spaces (with SQL Server 2017 solution)

  

 create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select * ,
RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' '))) AS NumSerie 

,len(RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')))
)
 ,datalength(RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')))
)

from test

drop table test

---SQL Server 2017
create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select *
,Rtrim(Trim('0' from col+' ')) ,
 len(Rtrim(trim('0' from col+' ')))
 ,datalength(Rtrim(trim('0' from col+' ')))
from test

drop table test

-- Syntax for SQL Server and Azure SQL Database
TRIM ( [ characters FROM ] string )

  

https://jingyangli.wordpress.com/2016/08/25/remove-leading-zeros-in-a-varchar-column-but-keep-inside-spaces/

 

https://docs.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-2017