COUNT depending on data

**Yannick Turgeon** · Jul 20 '05, 01:27 AM

Re: COUNT depending on data

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@poczt a.onet.pl> wrote in message
news:bjneen$l8r $1@news.onet.pl ...[color=blue]
> 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 |[/color]
etc...[color=blue]
> -------|----------|---------|-----------|----------|----------|-----------[/color]
|-[color=blue]
> -
> LA | 4 | 2 | 1 | 6 |
> 0 | 0 | 0
> VV | 3 | 7 | 0 | 20 |[/color]
0[color=blue]
> | 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[/color]
3[color=blue]
> years
> and more later then it will be round 40 temporary tables.
>
> Thanks for any response.
>
> Regards,
> Casper
>
>
>
>
>
>[/color]

**Kaczorek** · Jul 20 '05, 01:28 AM

Re: COUNT depending on data

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

Regards,
Casper

U¿ytkownik "Yannick Turgeon" <nobody@nowhere .com> napisa³ w wiadomo¶ci
news:9cK7b.3255 $z5.259277@news 20.bellglobal.c om...[color=blue]
> Casper,
>
> What you need is a crosstab query. SQL Server do not have this[/color]
functionality[color=blue]
> as Access or Excel have. It's feasable but not very easy. Take a look[/color]
here:[color=blue]
> 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@poczt a.onet.pl> wrote in message
> news:bjneen$l8r $1@news.onet.pl ...[color=green]
> > 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 |[/color]
> etc...
>
> -------|----------|---------|-----------|----------|----------|-----------
> |-[color=green]
> > -
> > LA | 4 | 2 | 1 | 6 |
> > 0 | 0 | 0
> > VV | 3 | 7 | 0 | 20 |[/color]
> 0[color=green]
> > | 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[/color][/color]
have[color=blue]
> 3[color=green]
> > years
> > and more later then it will be round 40 temporary tables.
> >
> > Thanks for any response.
> >
> > Regards,
> > Casper
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]

**Kaczorek** · Jul 20 '05, 01:28 AM

Re: COUNT depending on data

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

Regards,
Casper

U¿ytkownik "Yannick Turgeon" <nobody@nowhere .com> napisa³ w wiadomo¶ci
news:9cK7b.3255 $z5.259277@news 20.bellglobal.c om...[color=blue]
> Casper,
>
> What you need is a crosstab query. SQL Server do not have this[/color]
functionality[color=blue]
> as Access or Excel have. It's feasable but not very easy. Take a look[/color]
here:[color=blue]
> 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@poczt a.onet.pl> wrote in message
> news:bjneen$l8r $1@news.onet.pl ...[color=green]
> > 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 |[/color]
> etc...
>
> -------|----------|---------|-----------|----------|----------|-----------
> |-[color=green]
> > -
> > LA | 4 | 2 | 1 | 6 |
> > 0 | 0 | 0
> > VV | 3 | 7 | 0 | 20 |[/color]
> 0[color=green]
> > | 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[/color][/color]
have[color=blue]
> 3[color=green]
> > years
> > and more later then it will be round 40 temporary tables.
> >
> > Thanks for any response.
> >
> > Regards,
> > Casper
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]

COUNT depending on data

COUNT depending on data

Comment

Comment

Comment