sql server - How Can I Use unpivot dynamically? -

this question has answer here:

• sql server dynamic pivot query? 6 answers
• sql server : columns rows 5 answers

i using ms sql server 2008r2, have table named category

profile   batch   doubles  feeder image  hardware           1       1       0      2       1  b          1       2       3      0       4           1       5       1      6       1           1       2       1      2       7 

where doubles, feeder, image, hardware event categories.

column names profile, batch fix, more event categories may added later. want sum of event categories individually unpivot , dynamic. expected output is,

eventcategory   occurence   doubles         10   feeder           5   image           10   hardware        13 

by more event categories may added later mean, when more categories added,i need these event categories added dynamically in unpivot query, eg expected output

 eventcategory   occurence       doubles         10       feeder           5       image           10       hardware        13       late             6       sensor          20        .               .        .               .        .               . --and on 

i have nothing tried yet, please suggest me dynamic unpivot query. hope clear question, appreciated.

i think need:

create table category (     [profile] varchar(10),     batch int,     doubles int,     feeder int,     [image] int,     hardware int )  insert category values  ('a', '1', '1', '0', '2', '1'),  ('b', '1', '2', '3', '0', '4'),  ('a', '1', '5', '1', '6', '1'),  ('a', '1', '2', '1', '2', '7')  declare @query nvarchar(max);  select @query = coalesce(     @query+char(10)+'union all'+char(10)+'select '+quotename(name,'''')+' eventcategory, sum('+quotename(name)+') occurence category',     'select '+quotename(name, '''')+' eventcategory, sum('+quotename(name)+') occurence category') sys.columns c [object_id]=object_id('category') , name<>'profile'  exec (@query)