27 Sept 2011

Pivot Table in SQL with dynamic columns

declare @columns varchar(max)
declare @convert varchar(max)
select @columns= STUFF((select '],[' + substring(SVQ_questiondesc,1,128) from TSurveyQuestions where SVQ_Svsrno = 25
order by  SVQ_Svorderno
for XML path('')),1,2,'') + ']'
print @columns

set @convert = 'select * from (select SVQ_Svsrno,SVQ_Svorderno,substring(SVQ_questiondesc,1,128) as SVQ_questiondesc from TSurveyQuestions where SVQ_Svsrno = 25) SQ
pivot( sum(SVQ_Svorderno) for SVQ_questiondesc in ('+ @columns +')) as pivottable
'
exec (@convert)

No comments:

Post a Comment