|
There is a table name "ResultData" which following data :
Rollno Result SchoolCode
1 First 1
2 First 1
3 Second 2
4 First 1
5 Fail 2
6 Third 2
7 Second 1
8 Fail 3
9 Fail 1
10 Second 2
I want the output like this :
(School code wise count of Result)
SchoolCode First Second Third Fail Total
1 3 1 0 1 5
2 0 2 1 1 4
3 0 0 0 1 1
Please write a single select query to get output.
Thanks in advance
Girish Kumar Sharma
|
|
|
|
|
I believe you want a "crosstab query". Apart from Microsoft products, I don't know if other vendors have it built-in. I don't believe it's part of the SQL standard...
Here is what Microsoft Access help says about cross tab queries.
"
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
Part Description
aggfunction An SQL aggregate function that operates on the selected data.
selectstatement A SELECT statement.
pivotfield The field or expression you want to use to create column headings in the query's result set.
value1, value2 Fixed values used to create column headings.
"
|
|
|
|
|
So, is it not possible to write a select statement in SQL Sever ?
|
|
|
|
|
Well, I thought it was included in SQL Server... Looks like didn't show-up until 2005.
Here is one article that may help. http://builder.com.com/5100-6388_14-6143761.html[^]
Once upon a time I wrote a lot of Perl code to dynamically do the same thing that the crosstab query does, but when I discovered crosstabs, I never went back. I don't know how to do it using CASE statements.
sorry I can't help more. Maybe someone else can...
|
|
|
|
|
You're correct, PIVOTs don't show up until 2005 in MSSQL
“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan
|
|
|
|
|
|
Thanks to all of you and finally i used this query :
SELECT SCHCODE,COUNT(CASE WHEN RESULT = 'First' THEN Result END) AS First,COUNT(CASE WHEN Result = 'Second' THEN Result END) AS Second,COUNT(CASE WHEN Result = 'Third' THEN Result END) AS Third,COUNT(CASE WHEN Result = 'Fail' THEN Result END) AS Fail,count(*) as Total FROM resultdata GROUP BY schcode
|
|
|
|
|
I'm not much of an SQL query expert,
But I think that I have a solution
SELECT<br />
SchoolCode,<br />
COUNT(First) As TotalFirst,<br />
COUNT(Second) As TotalSecond,<br />
COUNT(Third) As TotalThird,<br />
COUNT(Fail) As TotalFail,<br />
(COUNT(First) + COUNT(Second) + COUNT(Third) +COUNT(Fail)) As TotalTotal<br />
FROM ResultData<br />
GROUP BY (SchoolCode)<br />
<br />
I havent tested it.
Post a reply to let every body know if it works.
|
|
|
|
|
hello all. i was wondering if anyone could recommend a good SQL IDE. i currently use SQL Server 2000 and SQL Server 2005. one of my main requirements is CVS. in one of my projects, i need to replicate one database's stored procedures to numerous others, so i need to see what procedures have changed everytime i need to work on the project. if anyone has any recommendations for other solutions, those are welcome to.
thanks for any help anyone provides.
|
|
|
|
|
I use both the SQL Server Management Studio Express and Quest Software's Toad for SQL 2.0 Freeware. Both work well for me, you may want to look at them if you haven't already 
|
|
|
|
|
I would recommend you to use SQL Server Mangement Studio itself, but try to set up CVSNT ( Concurrent Versioning System - its free - Open source) and the great thing is you can integrate SQL Mangement Studio with CVS. You can use wincvs as a client to access the CVS Server. Setting up the CVS server and client are a breeze if you know what you are doing... There are plenty of resources on the net too.
So you can maintain source control easily with no cost. Mail me if you need further instructions on integrating SSMS with CVS.
|
|
|
|
|
Where is (LOCAL) determined on my machine? I have a local instance of SQL Server and am trying to get to it with that name but it just goes to my machine name instead of <machinename>\<sqlservername>.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Where is (LOCAL) determined on my machine? I have a local instance of SQL Server and am trying to get to it with that name but it just goes to my machine name instead of \.
It is the default instance of SQL Server on the local machine.
|
|
|
|
|
Where can I change that? My computer thinks I have SQL Express 2005 and when I installed SQL Server 2000 it obviously didnt get updated.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Where can I change that?
It is set when SQL Server is installed.
CleaKO wrote: My computer thinks I have SQL Express 2005 and when I installed SQL Server 2000 it obviously didnt get updated
I don't understand that statement.
SQL Server 2005 express edition usually installed itself as a named (not default) instance. The name is, I think, SQLEXPRESS.
The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
|
|
|
|
|
Colin Angus Mackay wrote: The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
This isnt a show stopper but Im just curious if after it is installed whether it can be changed by me. There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Im just curious if after it is installed whether it can be changed by me
Not that I am aware of.
CleaKO wrote: There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
That is most likely to permit SQL Server 2005 to use CLR Stored Procedures
|
|
|
|
|
It is probably set in your machine.config. I would look there. It can be found in your C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
the v2.0.50727 might be different depending on which version of .net you are running.
Hope that helps.
Ben
|
|
|
|
|
Is this the best way to extract just the date portion of a datetime field while preserving the fact that the field is a datetime, so that date range comparisons can be made (without dealing with the time portion)?
Convert(datetime, Convert(nvarchar, transactiondatetime, 101))
Example:
select amount from deposit where
Convert(datetime, Convert(nvarchar, transactiondatetime, 101)) >= Convert(datetime, @StartDate) and
Convert(datetime, Convert(nvarchar, transactiondatetime, 101)) <= Convert(datetime, @EndDate)
where StartDate and EndDate are passed in as strings with only the date component (like '1/30/2007')
Thanks!
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
When I do that, I usually ensure that the end date is fixed so that the time portion is set to 23:59 and the start date's time portion is 00:00.
Also, I never use culture dependent strings. It can cause all sorts of problems. I always use the culture neutral ISO format.
I would say that if your database was always going to be used in the US then it probably doesn't matter. But a few years ago I worked on a database that was always going to be used in the UK, but somewhere along the way the cultural settings got changed and all the date/string stuff failed. After that, I switched to ISO format and have never had any problems.
|
|
|
|
|
Colin Angus Mackay wrote: When I do that, I usually ensure that the end date is fixed so that the time portion is set to 23:59 and the start date's time portion is 00:00.
I considered that as well, but figured I'd at least look at how to do it strictly in SQL. The problem is that there's a front-end UI to the report for selecting the date range, which doesn't include time. This value is then sent up to the middleware (Interacx server) that handles the query. Of course, I put logic in the client to do as you suggested, I just haven't figured out the best way to do this, because the client, being of a generic nature (Interacx client), doesn't really *know* that it's a start or end date. It's just a field in a datetime control that's associated with a parameter in the query. Gotta figure out the best way to do this.
Colin Angus Mackay wrote: I would say that if your database was always going to be used in the US then it probably doesn't matter.
Yes, it is. I considered culture issues, but this is strictly for turnkey, intranet systems here in the US. And there's already a competitor in Germany for this  system.
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
Marc Clifton wrote: Of course, I put logic in the client to do as you suggested, I just haven't figured out the best way to do this, because the client, being of a generic nature (Interacx client), doesn't really *know* that it's a start or end date. It's just a field in a datetime control that's associated with a parameter in the query. Gotta figure out the best way to do this.
I used to have a UDF in SQL Server that did that for me. Pity I don't work for that company any more, otherwise I could have found out how I did it. I just don't remember off the top of my head.
|
|
|
|
|
When I have done this type of stuff before usually I just worry about getting the @startdate and @enddate correct. That way I don't have to mess wtih the transactiondatetime which could be a lot of rows that I really don't want to do a convert with.
Usually I want the @startdate to have a time portion of 00:00:00 and the @enddate to have 23:59:59. Since what is passed in at the @startdate and @enddate will have a time on it I usually wack if off and add the correct time and covert it to a datetime.
Hope that helps.
Ben
|
|
|
|
|
kubben wrote: That way I don't have to mess wtih the transactiondatetime which could be a lot of rows that I really don't want to do a convert with.
Ah, good point. I imagine the performance of what I'm doing is pretty bad.
kubben wrote: Hope that helps.
Yup. Thanks!
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
Hello Friends,
I have a problem with recordsets.
I m getting Rs.RecordCount = -1 while calling a store procedures
here is code -
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.RecordSet")
cmd.ActiveConnection = myCon
cmd.CommandText = "SP_Name"
cmd.CommandType = 4
cmd.CommandTimeout = 300
cmd.Parameters.Append cmd.CreateParameter("@par1",20,1,10,1)
cmd.Parameters.Append cmd.CreateParameter("@par2",20,1,10,1)
cmd.Parameters.Append cmd.CreateParameter("@par3",20,1,10,20)
cmd.Parameters.Append cmd.CreateParameter("@par4",20,1,10,Null)
Rs.CursorLocation = 3
Rs.LockType = 1
Rs.CursorType = 3
Set Rs = cmd.Execute
Response.Write "<br>RecordCount = " & Rs.RecordCount
pls give some usefull solution.
Thanks,
Ankur Bakliwal
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
