COUNT depending on data

Hello.

I have one serious problem with COUNT in TSQL.
I use MS SQL Server 2000.
I would like to count rows depending on data in it.
What I meat is:
I have fields like:
region | month | year | some_count |
---|---|--|----|
LA | 1 | 2003| 4 |
LA | 2 | 2003| 2 |
LA | 3 | 2003| 1 |
LA | 4 | 2003| 6 |
VV | 1 | 2003| 3 |
VV | 2 | 2003| 7 |
VV | 4 | 2003| 20 |
VV | 6 | 2003| 3 |
BB | 2 | 2002| 1 |
etc...

And what I would like to get from it is:

region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...
---|----|---|----|----|----|----|-
-
LA | 4 | 2 | 1 | 6 |
0 | 0 | 0
VV | 3 | 7 | 0 | 20 | 0
| 3 | 0
BB | 0 | 1 | 0 | 0 |
0 | 0 | 0
etc...

Is it possible to do it in views? if yes then how?
I could use temp tables for each month but it is not solution when I have 3
years
and more later then it will be round 40 temporary tables.

Thanks for any response.

Regards,
CasperCasper,

What you need is a crosstab query. SQL Server do not have this functionality
as Access or Excel have. It's feasable but not very easy. Take a look here:
http://www.mssqlserver.com/faq/development-crosstab.asp

As you'll see, you'll have to hard code your month column name and manualy
add one in your query.

HTH

Yannick

"Kaczorek" <kaczorck@.poczta.onet.pl> wrote in message
news:bjneen$l8r$1@.news.onet.pl...
> Hello.
> I have one serious problem with COUNT in TSQL.
> I use MS SQL Server 2000.
> I would like to count rows depending on data in it.
> What I meat is:
> I have fields like:
> region | month | year | some_count |
> ---|---|--|----|
> LA | 1 | 2003| 4 |
> LA | 2 | 2003| 2 |
> LA | 3 | 2003| 1 |
> LA | 4 | 2003| 6 |
> VV | 1 | 2003| 3 |
> VV | 2 | 2003| 7 |
> VV | 4 | 2003| 20 |
> VV | 6 | 2003| 3 |
> BB | 2 | 2002| 1 |
> etc...
> And what I would like to get from it is:
> region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 |
etc...
> ---|----|---|----|----|----|----
|-
> -
> LA | 4 | 2 | 1 | 6 |
> 0 | 0 | 0
> VV | 3 | 7 | 0 | 20 |
0
> | 3 | 0
> BB | 0 | 1 | 0 | 0 |
> 0 | 0 | 0
> etc...
> Is it possible to do it in views? if yes then how?
> I could use temp tables for each month but it is not solution when I have
3
> years
> and more later then it will be round 40 temporary tables.
> Thanks for any response.
> Regards,
> Casper
>
>|||Thanks verry much. This link is verry helpfull. This is exactly what I
nead.

Regards,
Casper

Uytkownik "Yannick Turgeon" <nobody@.nowhere.com> napisa w wiadomoci
news:9cK7b.3255$z5.259277@.news20.bellglobal.com...
> Casper,
> What you need is a crosstab query. SQL Server do not have this
functionality
> as Access or Excel have. It's feasable but not very easy. Take a look
here:
> http://www.mssqlserver.com/faq/development-crosstab.asp
> As you'll see, you'll have to hard code your month column name and manualy
> add one in your query.
> HTH
> Yannick
> "Kaczorek" <kaczorck@.poczta.onet.pl> wrote in message
> news:bjneen$l8r$1@.news.onet.pl...
> > Hello.
> > I have one serious problem with COUNT in TSQL.
> > I use MS SQL Server 2000.
> > I would like to count rows depending on data in it.
> > What I meat is:
> > I have fields like:
> > region | month | year | some_count |
> > ---|---|--|----|
> > LA | 1 | 2003| 4 |
> > LA | 2 | 2003| 2 |
> > LA | 3 | 2003| 1 |
> > LA | 4 | 2003| 6 |
> > VV | 1 | 2003| 3 |
> > VV | 2 | 2003| 7 |
> > VV | 4 | 2003| 20 |
> > VV | 6 | 2003| 3 |
> > BB | 2 | 2002| 1 |
> > etc...
> > And what I would like to get from it is:
> > region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 |
> etc...
> ---|----|---|----|----|----|----
> |-
> > -
> > LA | 4 | 2 | 1 | 6 |
> > 0 | 0 | 0
> > VV | 3 | 7 | 0 | 20 |
> 0
> > | 3 | 0
> > BB | 0 | 1 | 0 | 0 |
> > 0 | 0 | 0
> > etc...
> > Is it possible to do it in views? if yes then how?
> > I could use temp tables for each month but it is not solution when I
have
> 3
> > years
> > and more later then it will be round 40 temporary tables.
> > Thanks for any response.
> > Regards,
> > Casper|||Thanks verry much. This link is verry helpfull. This is exactly what I
nead.

Regards,
Casper

Uytkownik "Yannick Turgeon" <nobody@.nowhere.com> napisa w wiadomoci
news:9cK7b.3255$z5.259277@.news20.bellglobal.com...
> Casper,
> What you need is a crosstab query. SQL Server do not have this
functionality
> as Access or Excel have. It's feasable but not very easy. Take a look
here:
> http://www.mssqlserver.com/faq/development-crosstab.asp
> As you'll see, you'll have to hard code your month column name and manualy
> add one in your query.
> HTH
> Yannick
> "Kaczorek" <kaczorck@.poczta.onet.pl> wrote in message
> news:bjneen$l8r$1@.news.onet.pl...
> > Hello.
> > I have one serious problem with COUNT in TSQL.
> > I use MS SQL Server 2000.
> > I would like to count rows depending on data in it.
> > What I meat is:
> > I have fields like:
> > region | month | year | some_count |
> > ---|---|--|----|
> > LA | 1 | 2003| 4 |
> > LA | 2 | 2003| 2 |
> > LA | 3 | 2003| 1 |
> > LA | 4 | 2003| 6 |
> > VV | 1 | 2003| 3 |
> > VV | 2 | 2003| 7 |
> > VV | 4 | 2003| 20 |
> > VV | 6 | 2003| 3 |
> > BB | 2 | 2002| 1 |
> > etc...
> > And what I would like to get from it is:
> > region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 |
> etc...
> ---|----|---|----|----|----|----
> |-
> > -
> > LA | 4 | 2 | 1 | 6 |
> > 0 | 0 | 0
> > VV | 3 | 7 | 0 | 20 |
> 0
> > | 3 | 0
> > BB | 0 | 1 | 0 | 0 |
> > 0 | 0 | 0
> > etc...
> > Is it possible to do it in views? if yes then how?
> > I could use temp tables for each month but it is not solution when I
have
> 3
> > years
> > and more later then it will be round 40 temporary tables.
> > Thanks for any response.
> > Regards,
> > Caspersql