tsql - sql server - transforming data with dynamic pivot -

this question has answer here:

• sql server dynamic pivot query? 6 answers

i need dynamically transform data in table #table format:

 spot_id        name        pct -------        ----        --- 1                         2 1               b           8 1               c           6 2                         4 2               b           5 3                         5 3               d           1 3               e           4 

to:

 spot_id         b     c     d     e -------    ---   ---   ---   ---   --- 1           2     5     6     0     0 2           4     5     0     0     0 3           5     0     0     1     4 

the thing don't know in advance values of column "name" or how many of them there are, think have use kind of dynamic sql pivoting

---

just figured out how solve problem:

  declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(name)                      (select distinct name #table) t             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = n'select spot_id,' + @cols + n'               (                 select spot_id, name, pct                 #table             ) x             pivot              (                 max(pct)                 name in (' + @cols + n')             ) p '  exec sp_executesql @query;  

if there more elegant way same?

declare @t table (spot int,name varchar(1),pct int) insert @t(spot,name,pct)values(1,'a',2),(1,'b',8),(1,'c',6),(2,'a',4),(2,'b',5),(3,'a',5),(3,'d',1),(3,'e',4)   select spot,isnull([a],0)[a],isnull([b],0)[b],isnull([c],0)[c],isnull([d],0)[d],isnull([e],0)[e] (select spot,pct,name @t)t pivot(max(pct) name in ([a],[b],[c],[d],[e]))p   select spot,isnull(max(case when name = 'a' pct end),0)a,isnull(max(case when name = 'b' pct end),0)b , isnull(max(case when name = 'c' pct end),0)c , isnull(max(case when name = 'd' pct end),0)d ,isnull(max(case when name = 'e' pct end),0)e  @t group spot 

using dynamic query :

if object_id('tempdb..#t') not null     drop table #t  create  table #t(spot int,name varchar(1),pct int) insert #t(spot,name,pct)values(1,'a',2),(1,'b',8),(1,'c',6),(2,'a',4),(2,'b',5),(3,'a',5),(3,'d',1),(3,'e',4)  declare @statement nvarchar(max) ,@columns nvarchar(max)  select @columns = isnull(@columns + ', ', '') + n'[' + tbl.name + ']' (    select distinct name    #t    ) tbl  select @statement = ' select spot,isnull([a],0)[a],isnull([b],0)[b],isnull([c],0)[c],isnull([d],0)[d],isnull([e],0)[e] (select spot,pct,name #t)t pivot(max(pct) name in (' + @columns + ')) pvt'  exec sp_executesql @statement = @statement