Hi

How to create template columns dynamically?

I am generating my resultset using Pivot sql statement. My query will return different columns everytime it runs. but the number of columns are same(8).

Hence I dont know my column names in advance. So I cannot create bound columns or template columns in design time to bind my dataset to webdatagrid's datasource with Autogeneratecolumns=false.

I need to convert the columns into template columns to show images based on the column value.

Please see the follwoing query and sample output and suggest me in desiging a webdatagrid.

Thanks in advance.

sample query:
select Name, [05/31/2012] as [05/31],[06/01/2012] as [06/01],[06/02/2012] as [06/02],[06/03/2012] as [06/03],[06/04/2012] as [06/04],[06/05/2012] as [06/05],[06/06/2012] as [06/06]
from
(
select b.dashboardstatus,a.name,convert(varchar(10),b.scorecarddate,101) as scorecarddate from [_Scorecard].[Processes] a inner join [_Scorecard].[ProcessDailySummary] b on a.processid=b.processid and b.scorecarddate between '04/01/2012' and '06/06/2012'
)
as p PIVOT
(
sum(dashboardstatus)
for
scorecarddate in ( [05/31/2012],[06/01/2012],[06/02/2012],[06/03/2012],[06/04/2012],[06/05/2012],[06/06/2012]
)
)
as pvt order by pvt.name

Sample Result:

Name 05/31 06/01 06/02 06/03 06/04 06/05 06/06 -- (Columns)
Factory Unit Info Import 5 5 NULL NULL 5 5 NULL
FactoryProductionControl.DoTheWorkImport 5 5 NULL NULL 5 5 NULL
FactoryProductionControl.FactoryProductionImportFile 5 5 NULL NULL 5 5 NULL
FactoryProductionControl.FactoryProductionProcessMakeRecords 5 5 NULL NULL 5 5 NULL
FactoryProductionControl.TestRecordProcessException NULL NULL NULL NULL NULL NULL 5
IDSVerificationControl.IDSVerification 5 5 NULL NULL 5 5 NULL
IDSVerificationControl.ShowCompressedData 5 5 NULL NULL 5 5 NULL
ShippedUnitNotesControl.BuildShippedUnitNotes 2 NULL NULL NULL 3 3 NULL
ShipUnitsControl.ProcessforCBPClaims 5 5 NULL NULL 5 5 NULL
WLCDataBuildControl.SyncShippedUnitsToWLC 5 5 NULL NULL 5 5 NULL