Msg 102, Level 15, State 1, Line 33 Incorrect syntax near ')'

Question

First time I am trying dynamic query as pivot table. I solve many error using SO. Now I am stuck in following common error.

Msg 102, Level 15, State 1, Line 33 Incorrect syntax near ')'.

So I can't discover what mistake is..!

pivotEx

create table pivotEx (name varchar(1), mark int, subject varchar(1))

insert into pivotEx values
 ('a', 70,'t')
,('a', 80,'e')
,('b', 60,'t')
,('c', 80,'t')
,('c', 90,'e')
,('c', 40,'m')

Static Query(working fine)

 select name, [e],[m],[t] from(  
  select name, mark, subject from pivotEx 
 ) f
 pivot
 ( sum(mark) for subject in ([e],[m],[t])
 ) p

Dynamic Query(what I am try)

declare @col varchar(max)
declare @sql nvarchar(100)
set @col = N''
select @col +=  ',' + col from (select distinct quotename(subject) col from pivotex) colp
select @col=SUBSTRING(@col,2,len(@col))
select  @col
set @sql = 
N'select name, '+@col+' from(  
 select name, mark, subject from pivotEx 
) f
pivot
( sum(mark) for subject in ('+@col+')
) p'

EXEC sp_executesql @sql

Note:

1. Using SQL Server 2014.
2. Using SSMS 2012.
3. I am new to Dynamic Query.

Thanks,

TamilPugal

Answer 1

You need to change @sql data type because your query string is truncated:

declare @sql nvarchar(MAX)

DBFiddle Demo

---

It is a good practice to print your query with SELECT/PRINT before execution.

PRINT @sql
-- select name, [e],[m],[t] 
   from(select name, mark, subject from pivotEx ) f pivot( sum(mark) for subje