'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 |
|---|
