Execute SimpleXTab 'Week_Number', '#UnitWeeksTemp', '',
'Sum','Week_Number', 'Unit_Number'

drop table #UnitWeeksTemp
*******

this works and allow me to use my own critera to build the temp table!

But I will need more than the "4000 nvarchar" limit during production.

I tried to modify the SimpleXTab procedure as follows.
I do not understand how to create the "make table" and execute it using the 'into ##temptbl_XTabTalbe'.
To create the table should I create all the needed fields. The table is not found.

AT this point I am not checking for the size of the the strings just getting the into table to work"
If you have the code created for the northwind could you demo that checking for the string length of 4000.
****
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

***************
ALTER PROCEDURE [dbo].[SimpleXTab] @XField varChar(20), @XTable varChar(20),
@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(20), @XRow varchar(40)
AS
Declare @SqlStr nvarchar(4000)
Declare @tempsql nvarchar(4000)
Declare @SqlStrCur nvarchar(4000)
Declare @col nvarchar(100)

CREATE TABLE ##temptbl_XTabTable (
[Unit_Number] [nvarchar] (10) ,
[Week_Number] [int] NULL ,
[Availability] [char] (1),
[Sort] [bit]

)
set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']'

select @sqlstrcur --
exec sp_executesql @sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor

open xcursor

Fetch next from xcursor
into @Col

While @@Fetch_Status = 0
Begin
set @Sqlstr = @Sqlstr + ", "
set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +
"' then [" + @XFunctionField + "] Else 0 End) As [" + 'Wk' + @Col + "]" ,'')
set @Sqlstr = @tempsql
Fetch next from xcursor into @Col

End

Select @Sqlstr as [mk], len(@sqlstr) as [leng] --

set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + ' into ##temptbl_XTabTable ' +
' From ' + @XTable +
@XWhereString + ' Group by ' + @XRow
set @Sqlstr = @tempsql

Close xcursor
Deallocate xcursor

set @tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @tempsql

Select @Sqlstr as [mk], len(@sqlstr) as [leng] --

exec ('Select * from ##temptbl_XTabTable')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*******************************