Combined aggregates into a single SQL table

Question

Ok, this is something I'm sure I know how to do under normal circumstances, but my brain apparently doesn't want to co-operate with me today. (Speaking of which, if you can think of a better title, that would also be useful...)

We have a table which is a bit like this:

Year Episode  Code
2000       1  A001
2000       1  A001
2000       1  C007
2000       2  A001
2000       2  B001

It's referencing another table, in which the combination of Year and Episode is unique, but this table I'm working with just lists the selection of codes applied to each episode.

What I'm trying to do is create a table that returns a per-year count of total episodes and episodes that have a particular code. I can't just do a simple "COUNT(*)" of the code, because one episode may have the same code multiple times.

Can anyone see a viable way to do what I am attempting?

Answer 1

This might be what you are after. You need at least SQL Server 2005 for the pivot function.

create table MyTable (
    [Year] datetime,
    Episode int,
    Code nvarchar(20)
)

insert into MyTable values ('01-01-2000', 1, 'A001')
insert into MyTable values ('01-01-2000', 1, 'A001')
insert into MyTable values ('01-01-2000', 1, 'C007')
insert into MyTable values ('01-01-2000', 2, 'A001')
insert into MyTable values ('01-01-2000', 2, 'B001')
insert into MyTable values ('01-01-2000', 2, 'B001')
insert into MyTable values ('01-01-2001', 1, 'A001')
insert into MyTable values ('01-01-2002', 1, 'A001')
insert into MyTable values ('01-01-2003', 1, 'C007')

select [Code], [2000], [2001], [2002]
from (
      select Code,     
      DATEPART(year, [Year]) as date,
      count(Episode) as instances
      from MyTable
      group by DATEPART(year, [year]), code) as o
pivot
(
    sum(instances) for date in ([2000], [2001], [2002])
) as p