How to do a cross tab where the number of columns varies? (ADO SQL Server) -

i'm trying write query has variable number of columns depending on data i've never done kind of thing.

we're running ado , hope have single query (possibly subqueries) no other coding or go statements, stored procedures, etc.

we're planning use results of query in editable grid.

below sample of our data. have list of employees , list of projects. note isn't "summed" cross tab. there's 1 source number per cell.

we want query results have 1 column each project. cells in column contain hours employee on project.

if add project, want column appear in query results.

edit: since we're writing query in code , submitting it, can generate query dynamically. need not dynamically generate code. example, in our data below, we'll able read (in our native language) project table , know have 3 projects , names are. can use them in pivot i'm seeing reading up, not sure how...

how this: http://sqlfiddle.com/#!6/2ded1/6

declare @pivotquery nvarchar(max) declare @columnname nvarchar(max) select @columnname= isnull(@columnname + ',','')        + quotename(name) (select name project) t2 set @pivotquery =       n'with t1 (          select ph.employee, e.name empname, p.name, ph.hours          project_hours ph          inner join project p          on p.id = ph.project          inner join employees e          on e.id = ph.employee        )        select *        t1        pivot(sum(hours) name in (' + @columnname + ')) pivot_table' exec sp_executesql @pivotquery 

with significant , further explanation here: http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

edit: read question again, notice you're building query in code, in case don't need utility above, simple pivot build for name in clause in program, this: http://sqlfiddle.com/#!6/2ded1/12

with t1 (   select ph.employee, e.name empname, p.name, ph.hours   project_hours ph   inner join project p   on p.id = ph.project   inner join employees e   on e.id = ph.employee ) select * t1 pivot(sum(hours)       name in ([first floor], [basement], [parking lot a]))       hours_summary