c# - advice for SQL query with row values as columns -

what best way go returning result set distinct zone values columns below basic select query?

select weight, base_rate, zone  rates  modeid = 984  

here table:

rates table:

    id int     modeid int     base_rate decimal     zone varchar     weight decimal 

i tried creating pivot query accomplish this, far have been unsuccessful. ideas? should manipulating results in c# code instead? results put gridview.

this result set like

weight zone1 zone2 zone3 zone4 5       5.50 5.65  5.75  6.00 10      5.55 6.00  7.00  8.00 15      7.50 8.00  9.00  10.00 

here failed attempt @ creating pivot query.

select * (   select weight, base_rate, zone rates modeid=984  ) d pivot (   max(weight)   zone in (select distinct zone d) ) piv; 

sql server not allow using select statement list of columns pivot. columns names must know @ run-time.

if have limited number of values, need hard code query:

select weight, zone1, zone2, zone3, zone4 (   select weight, base_rate, zone    rates modeid=984  ) d pivot (   max(base_rate)   zone in (zone1, zone2, zone3, zone4) ) piv; 

but if going have unknown number of values, need @ using dynamic sql. create sql string executed @ run-time. syntax similar to:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(zone)                      rates                      modeid=984                      group zone                     order zone             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select weight,' + @cols + '                           (                 select weight, base_rate, zone                  rates modeid=984              ) x             pivot              (                 max(base_rate)                 zone in (' + @cols + ')             ) p '  execute sp_executesql @query;