sql server - Dynamic Pivot with varying columns -

sql server - Dynamic Pivot with varying columns -

i have poa code dynamic pivot pulls info dx temp table , inserts info temp poa table. issue i'm having there possibility of 35 different columns can returned. depending on month there 15 columns (poa1...poa15) or there 35 columns (poa1...poa35). bring together dynamic pivot temp table on patient table. problem is, need show 35 columns if of columns not exist in temp poa table.

--pivot dx poa codes declare @poaname varchar(40) select @poaname = '##tmppoa' declare @colspoa nvarchar(2000) select @colspoa = stuff((select distinct top 100 percent '],[' + 'poa' + cast(dx.rownum nvarchar) #tmpdx dx order '],[' + 'poa' + cast(dx.rownum nvarchar) xml path ('') ),1,2,'') + ']' declare @querypoa nvarchar(4000) set @querypoa = 'n select encobjid, '+ @colspoa +' ' + poaname + ' (select dx.encobjid ,''poa'' + dx.rownum rownum ,dx.poamne #tmpdx dx ) p pivot ( min([poamne]) rownum in ( ' + @colspoa + ' ) ) pvt' execute(@querypoa)

i'm receiving invalid column name in patient query because of columns don't exist in ##tmppoa. thought creating temp table called #tmpdxpoa , doing insert (insert #tmpdxpoa select * ##tmppoa), doesn't work (i receive column name or number of supplied values not match error).

any thoughts on how create 35 columns if there isn't data? don't care if they're null, need have place holders in main patient query , doesn't help number of columns returned varies every month.

with help of @mxix able come following:

declare @poasql nvarchar(max) set @poasql = n'insert #tmppoafinal (encobjid,'+@colspoa+') select * ##tmppoa' execute(@poasql)

i set after execute(@querypoa) in main query.

sql-server sql-server-2008 pivot dynamicquery dynamic-pivot