How Can I Use unpivot dynamically?
<p>I am Using MS SQL Server 2008R2, I have a Table named <code>Category</code></p>

<pre><code>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
</code></pre>

<p>Where <code>Doubles</code>, <code>Feeder</code>, <code>Image</code>, <code>Hardware</code> are Event Categories.</p>

<p>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,</p>

<pre><code>EventCategory   Occurence
  Doubles         10
  Feeder           5
  Image           10
  Hardware        13
</code></pre>

<p>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</p>

<pre><code> EventCategory   Occurence
      Doubles         10
      Feeder           5
      Image           10
      Hardware        13
      Late             6
      Sensor          20
       .               .
       .               .
       .               . --And so on
</code></pre>

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