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
groupedmonth
. - 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.
Comments
Post a Comment