Alter procedure CrossTab3        
/*--        

EXEC CrossTab3 'select category,content from tbmodule tm inner join tblContent tf on tm.[lineno]=tf.linenoid 
where link like ''%reg_acknowledgement.asp%'' and tf.language=''english'' ', 'category', 'max(content)[]', 'category' 

--*/         
(          
 @select varchar(2000),          
 @PivotCol varchar(100),           
 @Summaries varchar(100),            
 @GroupBy varchar(100)          
 --@OtherCols varchar(100) = Null          
)          
          
AS          
          
set nocount on          
set ansi_warnings off          
          
declare @sql varchar(8000)          
declare @sql1 varchar(8000)       
          
Select @sql = ''          
--Select @OtherCols= isNull(', ' + @OtherCols,'')          
          
          
create table #pivot_columns  (pivot_column_name varchar(100))          
Select @sql='select ''' + replace( + @PivotCol,',','''   as pivot_column_name union all select ''')+''''          
insert into #pivot_columns          
exec(@sql)          
          
select @sql=''          
          
create table #pivot_columns_data  (pivot_column_name varchar(100),pivot_column_data nText)          
          
Select @PivotCol=''          
Select @PivotCol=max(pivot_column_name) from #pivot_columns          
While @PivotCol>''          
Begin          
 insert into #pivot_columns_data(pivot_column_name,pivot_column_data)           
 exec  (          
  'select distinct ''' + @PivotCol +''' as pivot_column_name, ''' + @PivotCol + ''' as pivot_column_data from           
   ('+          
    @select          
   +'          
   ) T'          
  )          
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol          
          
end          
          
          

select @sql = @sql + ', ' +             
  replace(          
  replace(          
   @Summaries,      
   '(','(CASE WHEN ' + Pivot_Column_name + '=''' +           
   Pivot_Column_data +  ''' THEN '           
   ),          
  ')[', ' END) as [' + pivot_column_data         
   )          
from #pivot_columns_data          
order by pivot_column_name  

--print @sql 
    
         
select @sql1= right(@sql,len(@sql)-1)      
      
           
exec ( 'select ' + @sql1 +          
          
       '  from (          
  '+          
   @select           
  +'          
  ) T '         
 )          
      
      
      
       
          
drop table #pivot_columns          
drop table #pivot_columns_data          
          
set nocount off          
set ansi_warnings on          

quote:

---

Originally posted by micnie_2020

Dear All,

I getting error on below sp:- Invalid operator for data type. Operator equals add, type equals ntext.

Alter procedure CrossTab3        
/*--        

EXEC CrossTab3 'select category,content from tbmodule tm inner join tblContent tf on tm.[lineno]=tf.linenoid 
where link like ''%reg_acknowledgement.asp%'' and tf.language=''english'' ', 'category', 'max(content)[]', 'category' 

--*/         
(          
 @select varchar(2000),          
 @PivotCol varchar(100),           
 @Summaries varchar(100),            
 @GroupBy varchar(100)          
 --@OtherCols varchar(100) = Null          
)          
          
AS          
          
set nocount on          
set ansi_warnings off          
          
declare @sql varchar(8000)          
declare @sql1 varchar(8000)       
          
Select @sql = ''          
--Select @OtherCols= isNull(', ' + @OtherCols,'')          
          
          
create table #pivot_columns  (pivot_column_name varchar(100))          
Select @sql='select ''' + replace( + @PivotCol,',','''   as pivot_column_name union all select ''')+''''          
insert into #pivot_columns          
exec(@sql)          
          
select @sql=''          
          
create table #pivot_columns_data  (pivot_column_name varchar(100),pivot_column_data varchar(max))          
          
Select @PivotCol=''          
Select @PivotCol=max(pivot_column_name) from #pivot_columns          
While @PivotCol>''          
Begin          
 insert into #pivot_columns_data(pivot_column_name,pivot_column_data)           
 exec  (          
  'select distinct ''' + @PivotCol +''' as pivot_column_name, ''' + @PivotCol + ''' as pivot_column_data from           
   ('+          
    @select          
   +'          
   ) T'          
  )          
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol          
          
end          
          
          

select @sql = @sql + ', ' +             
  replace(          
  replace(          
   @Summaries,      
   '(','(CASE WHEN ' + Pivot_Column_name + '=''' +           
   Pivot_Column_data +  ''' THEN '           
   ),          
  ')[', ' END) as [' + pivot_column_data         
   )          
from #pivot_columns_data          
order by pivot_column_name  

--print @sql 
    
         
select @sql1= right(@sql,len(@sql)-1)      
      
           
exec ( 'select ' + @sql1 +          
          
       '  from (          
  '+          
   @select           
  +'          
  ) T '         
 )          
      
      
      
       
          
drop table #pivot_columns          
drop table #pivot_columns_data          
          
set nocount off          
set ansi_warnings on          

Please advise. If the content in Varchar format, then it's working fine.

Pls advise

Regards,
Micheale

---