# 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_no_truncate = 1,

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 )