How Can I Use unpivot dynamically?

Question

I am Using MS SQL Server 2008R2, I have a Table named Category

Profile   Batch   Doubles  Feeder Image  Hardware
 A          1       1       0      2       1
 B          1       2       3      0       4
 A          1       5       1      6       1
 A          1       2       1      2       7

Where Doubles, Feeder, Image, Hardware are Event Categories.

Column names Profile, Batch are fix, But more Event Categories may be added later. I want to sum of all Event categories individually with unpivot and dynamic. My expected output is,

EventCategory   Occurence
  Doubles         10
  Feeder           5
  Image           10
  Hardware        13

By more Event Categories may be added later I mean, When more Categories will be added,I need these Event Categories to be added dynamically in the unpivot query, Eg Expected Output

 EventCategory   Occurence
      Doubles         10
      Feeder           5
      Image           10
      Hardware        13
      Late             6
      Sensor          20
       .               .
       .               .
       .               . --And so on

I have nothing tried yet, Please suggest me the dynamic unpivot query. Hope I am clear with the question, Any help would be appreciated.

Answer 1

I think this is what you 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 FROM Category',
    'SELECT '+QUOTENAME(name, '''')+' EventCategory, SUM('+QUOTENAME(name)+') Occurence FROM Category')
FROM sys.columns C WHERE [object_id]=OBJECT_ID('Category')
AND name<>'Profile'

EXEC (@query)