COUNT depending on data

[Kaczorek]

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

[Yannick Turgeon]

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" <ka******@poczta.onet.pl> wrote in message
news:bj**********@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

[Kaczorek]

Thanks verry much. This link is verry helpfull. This is exactly what I
nead.

Regards,
Casper
U¿ytkownik "Yannick Turgeon" <no****@nowhere.com> napisa³ w wiadomo¶ci
news:9c******************@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" <ka******@poczta.onet.pl> wrote in message
news:bj**********@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

[Kaczorek]

Thanks verry much. This link is verry helpfull. This is exactly what I
nead.

Regards,
Casper
U¿ytkownik "Yannick Turgeon" <no****@nowhere.com> napisa³ w wiadomo¶ci
news:9c******************@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" <ka******@poczta.onet.pl> wrote in message
news:bj**********@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