sql server - Create a dynamic table based on existing table result in sql -

this question has answer here:

• sql server 2008 vertical data horizontal 3 answers

this actual table.

month   rcount  cond_id cond_desc jan-13  52  -1  n/a jan-13  194 0   normal jan-13  86  2   abnormal feb-13  54  -1  n/a feb-13  158 0   normal feb-13  110 2   abnormal mar-13  14  -1  n/a mar-13  113 0   normal mar-13  90  2   abnormal apr-13  3   -1  n/a apr-13  259 0   normal apr-13  144 2   abnormal may-13  10  -1  n/a may-13  693 0   normal may-13  305 2   abnormal may-13  1   4   critical jun-13  169 0   normal jun-13  36  2   abnormal jun-13  1   4   critical 

i need following results.

r_id    cond_id cond_desc   jan-13  feb-13  mar-13  apr-13  may-13  jun-13 1   -1  n/a         51  54  14  3   10  169 2   0   normal          194 158 113 259 693 36 3   2   abnormal    86  110 90  144 305 1 4   0   critical    0   0   0   0   1   0 

assuming wont this:

• sum values of rcount grouped month.
• the value of cond_id in example wrong, example case critical value should 4.
• the field r_id (rownumber) count of query.

the following query maybe useful:

declare @cols nvarchar(max), @query  nvarchar(max)  select @cols = stuff((select ',' + quotename([month])                      mytable                     group [month]                     order [month]             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select row_number() on (order cond_id) r_id,cond_id,cond_desc,' + @cols + '                           (               select                   cond_id,                   cond_desc,                  rcount,                  month                mytable             ) x             pivot              (                 sum(rcount)                 [month] in (' + @cols + ')             ) p '  execute sp_executesql @query; 

here try sql fiddle. query i've created bluefeet's link.