Report group by Date Range

[aauxta]

I have got this table:

[tt]
DateEntered code
----------- --------
1/1/2004 abc
1/1/2004 def
1/5/2004 def
2/2/2004 abc
2/9/2004 xyz
4/1/2004 abc
4/1/2004 xyz
[/tt]
and I want to turn it into:
[tt]
code 1/2004 2/2004 3/2004 4/2004
----- ------- ------ ------ -------
abc 1 1 0 1
def 2 0 0 0
xyz 0 1 0 1
[/tt]
I have spent many hours trying to figure it out but with my limited knowledge in sql I just have no luck at all. Is it even possible to create the table with the month as header?

Thanks in advance.

 

[donutman]

What you want to do is run a Cross Tab query. This FAQ will help you learn how to do it and/or do it for you.
Crosstab Query - PART I (Code Generator) faq183-5269
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

Try:

select code as code,
count(case when datepart(mm, dateentered) = 1
then 1 else 0 end ) as "1/2004",
count(case when datepart(mm, dateentered) = 2
then 1 else 0 end ) as "2/2004",
count(case when datepart(mm, dateentered) = 3
then 1 else 0 end ) as "3/2004",
count(case when datepart(mm, dateentered) = 4
then 1 else 0 end ) as "4/2004"
from myTable
group by code

 

[maswien]

ops, not count, should be sum()

select code as code,
sum(case when datepart(mm, dateentered) = 1
then 1 else 0 end ) as "1/2004",
sum(case when datepart(mm, dateentered) = 2
then 1 else 0 end ) as "2/2004",
sum(case when datepart(mm, dateentered) = 3
then 1 else 0 end ) as "3/2004",
sum(case when datepart(mm, dateentered) = 4
then 1 else 0 end ) as "4/2004"
from myTable
group by code

 

[aauxta]

Thanks mija and it sure gets me a clear fresh start.