'Error in SQL query (Incorrect syntax near the keyword 'with'. If this statement is a common table expression...)

I am very new in this site. I have a query that pivot 2 joins query. I am getting Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause, or a change tracking context clause, the previous statement must be terminated with a semicolon. I been trying to put semicolon but I still don't get it work.

Here you have the query in question.

Declare @sql nvarchar(max)
Declare @columns nvarchar(max)

declare @orgcols table(
[QuestionID] nvarchar(max)
)

insert into @orgcols
Select distinct a.[QuestionID] + ' -  ' + a.[GURSRVQ_TEXT] as [QuestionID]
from [MappingNew4] a 
left join [ICE_SURVEY_ANSWERS_MVCleaned] b
on a.[GUBSRVY_TITLE] = b.[GUBSRVY_TITLE]
join [ICE_SURVEY_ANSWERS_SUMMARY_MV] c
on b.[GUBSRVY_TITLE] = c.[GUBSRVY_TITLE]
where TERM = 'ABC';

;With combinedcolumntab as(
select distinct
STUFF((Select ','+[QuestionID]
from @orgcols T1
FOR XML PATH('')),1,1,'') as col from @orgcols T2
)
select @columns = col from combinedcolumntab

set @sql = N'With org as
(Select
b.TOKEN,a.[GUBSRVY_TITLE], a.[QuestionID] + '' -  '' + a.[GURSRVQ_TEXT] as [QuestionID] ,replace(replace((cast(b.[GURSRVQ_TEXT] as NVarchar(100))),'','','';''),'''''''','''''''''''') as[GURSRVQ_TEXT],(cast(isnull([GORSRVR_RESPONSE_1], 0) as int) 
+ cast(isnull([GORSRVR_RESPONSE_2],0) as int) + cast(isnull([GORSRVR_RESPONSE_3],0) as int) 
+ cast(isnull([GORSRVR_RESPONSE_4],0) as int) + cast(isnull([GORSRVR_RESPONSE_5],0) as int))
as RESPONSE
from [MappingNew4] a 
left join [ICE_SURVEY_ANSWERS_MVCleaned] b
on a.[GUBSRVY_TITLE] = b.[GUBSRVY_TITLE]
join [ICE_SURVEY_ANSWERS_SUMMARY_MV] c
on b.[GUBSRVY_TITLE] = c.[GUBSRVY_TITLE]
where TERM = ''ABC''
)
Select *
From Org
PIVOT
(
sum(RESPONSE)
FOR QuestionID
in ('+@columns+')
) as pvt'

EXECUTE sp_executesql @sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source