sql - Pivot query replacing column name instead of adding a new one -

i have dynamic pivot query using retrieve data. instead of adding new column query, replacing last column. therefore causing data binding issues 1 of controls on front end.

set @sql = 'with resulttable (' + char(13)     set @sql = @sql + 'select row_number() on (order censusid) rownum           ,quoteid, censusid, firstname, lastname, middlename, inputage, gender, salaryamount, salarytypeid, isdeleted,bcensuscount, vgtl, vstd ' + @colnames + ' ' + char(13)      set @sql = @sql + 'from (select f.censusid, columnname, columnvalue, quoteid, firstname, lastname, middlename, inputage, gender, salaryamount, salarytypeid, isdeleted,bcensuscount, vgtl, vstd ' + char(13)      set @sql = @sql + 'from #tblx f left outer join census.tbl_census c on f.censusid = c.censusid) p ' + char(13)     set @sql = @sql + 'pivot (max(columnvalue) columnname in ('  + @colnames + ')) pvt ' + char(13)     set @sql = @sql + ') select * resulttable ' + char(13)   set @sql = @sql + 'where rownum > ' + cast(@firstrec varchar) + ' , rownum < ' + cast(@lastrec varchar)      exec (@sql)  

vgtl , vstd columns added. vgtl shows fine, vtsd replaced different text. can't figure out causing column name replaced.

the problem in first line (select statement), when variable @colnames appended following line first column in variable @colnames become alias vstd.

 '........, isdeleted,bcensuscount, vgtl, vstd ' + @colnames + ' ' + char(13) 

therefor add comma after column vstd , should fix issue like....

 '........, isdeleted,bcensuscount, vgtl, vstd, ' + @colnames + ' ' + char(13)