sql - Pivot sample with dynamic columns -

i've seen many pivot examples didn't find applies case. hope can give me hand.

i have these 2 tables

 entities performance     +----------+--------+----------+ | identiry | idweek | idresult | +----------+--------+----------+ | 1        | 1      | 1        | | 2        | 1      | 1        | | 3        | 1      | 2        | | 1        | 2      | 3        | | 2        | 2      | 1        | | 3        | 2      | 2        | | 1        | 3      | 3        | | 2        | 3      | 1        | | …        | …      | …        | | 1        | 10     | 1        | +----------+--------+----------+ 

number of weeks dynamic
number of entities dynamic

 performance details      +----------+-------------+--------+ | idresult | description | color  | +----------+-------------+--------+ |        1 | bad         | red    | |        2 | average     | yellow | |        3 |        | green  | +----------+-------------+--------+ 

and desired output

            |               weeks                | +----------+---------+---------+------+---+-----+ | identity | 1       | 2       | 3    | … |  10 | +----------+---------+---------+------+---+-----+ |        1 | bad     |    | | … | bad | |        2 | bad     | bad     | bad  | … |     | |        3 | average | average |      | … |     | +----------+---------+---------+------+---+-----+ 

thanks!

this might close.

declare @weekstart int,         @weekend int  select  @weekstart = min(idweek),         @weekend = max(idweek)    dbo.weekstable  declare @weekcolumns varchar(max)  while @weekstart < @weekend begin     set @weekcolumns = coalesce(@weekcolumns + '],[', '[') + convert(varchar, @weekstart)     set @weekstart = @weekstart + 1 end set @weekcolumns = concat(@weekcolumns,'],[',convert(varchar, @weekstart),']')  declare @sql varchar(max) = ' select identity, ' + @weekcolumns + ' ( select  ep.identity,         ep.idweek,         pd.description    dbo.entitiesperformance ep         join dbo.performancedetails pd on ep.idresult = pd.idresult ) src pivot (     min(description)     idweek in (' + @weekcolumns + ') ) pvt ' exec (@sql)