<p>I have a database in the following format:</p>

<pre><code> ID    TYPE   SUBTYPE    COUNT   MONTH
 1      A      Z          1       7/1/2008
 1      A      Z          3       7/1/2008
 2      B      C          2       7/2/2008
 1      A      Z          3       7/2/2008
</code></pre>

<p>Can I use SQL to convert it into this:</p>

<pre><code>ID    A_Z   B_C   MONTH
1     4     0     7/1/2008
2     0     2     7/2/2008
1     0     3     7/2/2008
</code></pre>

<p>So, the <code>TYPE</code>, <code>SUBTYPE</code> are concatenated into new columns and <code>COUNT</code> is summed where the <code>ID</code> and <code>MONTH</code> match.</p>

<p>Any tips would be appreciated. Is this possible in SQL or should I program it manually?</p>

<p>The database is SQL Server 2005. </p>

<p>Assume there are 100s of <code>TYPES</code> and <code>SUBTYPES</code> so and 'A' and 'Z' shouldn't be hard coded but generated dynamically.</p>

```
```
select id,
sum(case when type = 'A' and subtype = 'Z' then [count] else 0 end) as A_Z,
sum(case when type = 'B' and subtype = 'C' then [count] else 0 end) as B_C,
month
from tbl_why_would_u_do_this
group by id, month
```
```
You change requirements more than our marketing team!  If you want it to be dynamic you'll need to fall back on a sproc.