Crass Tab Query

Hi!

I m using sql 2005.I have a table as showing below.

yearRegionloan_amtpur_idpurpose1981Andhra pradesh$20,000.00 1Animal Husbandary1981Arunachal Pradesh$110,000.00 1Animal Husbandary1981Assam$240,000.00 1Animal Husbandary1981Bihar$75,000.00 1Animal Husbandary1981Chhattisgarh$55,500.00 1Animal Husbandary1981Gujarat$77,500.00 1Animal Husbandary1982Goa$44,888.00 1Animal Husbandary1982Himachal pradesh$4,000.00 1Animal Husbandary1982Himachal pradesh$20,000.00 1Animal Husbandary1982Jammu and kashmir$30,000.00 1Animal Husbandary1882Jharkhand$35,000.00 1Animal Husbandary1982Karnataka$40,000.00 1Animal Husbandary1982Kerala $20,000.00 1Animal Husbandary1982Madhya pradesh$5,000.00 1Animal Husbandary

I want to produce report as by using crosstab query as showing bellow

Year Asam Hyadrabad goa arunachal pardesh etc.........

1981 1000.00 2000.00 8000.2 00000 000000 .....

1981

'

'

Is it possible by crosstab query ?or please suggest me another way as early as possible.

Thanx in advance.

Abhishek

http://www.databasejournal.com/features/mssql/article.php/10894_3516331_2

CPU usage 90%

hi
My site has become tremendous slow..cpu usage for normal transcationsalso showing 90%..day beefore it was working fine..
any hints wot can be the problems.Look at your indexes. As data gets added, and you do not have appropriate
indexes, the amount of data SQL has to scan through increases. This results
in high CPU usage.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:1582C1EB-6E00-4369-94D6-B047B067550F@.microsoft.com...
> hi
> My site has become tremendous slow..cpu usage for normal transcationsalso
showing 90%..day beefore it was working fine..
> any hints wot can be the problems.
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXS
Hello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default...b;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default...b;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?
|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/defaul...kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribió en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
>> Hello
>> We have running our server-client application on line. The server
>> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
>> (process sqlservr.exe) is the great consumer of memory. I am afraid a
>> collapse in the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribió en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> We have running our server-client application on line. The server
>> Win2000
>> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
>> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
>> the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS
>>
>

counting multiple values from the same column and grouping by a another column

This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.

TheTable
Order# Customer Status

STATUS has valid values of PROCESSED and INPROGRESS

The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.

I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?

Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customeryou can write a stored proc and use output parameters to return the values..and in your stored proc have multiple sql stmts to get the diff counts..

hth|||This might work for you, there is probably a better way, this can become very expensive with the 2 sub queries if there is a lot of data

 SELECT Customer,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'Shipped'
 ) AS Shipped,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'SomeOtherStatus'
 ) AS SomeOtherStatus
 FROM TheTable
 ORDER BY Customer