How to make Sql query for my problem

Question

1.00/5 (1 vote)

See more:

Hello Every One,

I am working in asp.net. I have a table MyClass. In this I have three columns.
Columns are classname, gender, house.

records are fill in following format:
ClassName Gender House
abc Male Rabbit
abc male bull
abc female rabbit
kbc male rabbit
kbc female bull
kbc male rabbit

My requirement is that i want to show records in following format.

classname, Male , Female, House
abc 1 1 rabbit
abc 1 0 bull
kbc 2 0 rabbit
kbc 0 1 bull

classname, Male , Female, House
will be column name in ouput. please help me that what query will be make for this.

Posted 9-Jul-12 1:02am

coolnavjot31

Updated 9-Jul-12 1:25am

v2

Add a Solution

Comments

Sandeep Mewara 9-Jul-12 7:12am

This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

What does Male/Female column values as 10 & 5 means? :doh:

[no name] 9-Jul-12 7:13am

"please help me that what query will be make for this"... instead of asking for some to do this for you, why not try something yourself? This is pretty much the most simple of SQL statements you can get.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arul R Ece · Accepted Answer · 2012-07-09T01:39:00

Solution 1

Hi..

Please use the below query.

It will help u

SQL

select [ClassName],[Male],[female],[House]
from
(select * from tblex)source pivot
(count(Gender) for Gender in ([Male],[female]))pt

order by ClassName

Posted 9-Jul-12 1:39am

Arul R Ece

Comments

Arul R Ece 9-Jul-12 7:42am

If it is help u means , accept this as answer

coolnavjot31 9-Jul-12 7:50am

Thanks it is working for me!

AshishChaudha 9-Jul-12 7:52am

Nice...my 4!

Arul R Ece 9-Jul-12 8:01am

Ok.....Thanks ..

P.S Vijay · Accepted Answer · 2012-07-09T02:01:00

Let's Try with this ...................

SQL

select ClassName
, SUM(case when gender = 'Male' then 1 else 0 end) as Male
, SUM(case when gender = 'Female' then 1 else 0 end) as Female 
, House 
from t1 
group by ClassName,House

OUTPUT

--------------------------------------------------
CalssName     Male    Female   House
--------------------------------------------------
abc	        1	0	bull
kbc	        0	1	bull
abc	        1	1	Rabbit
kbc	        2	0	Rabbit
--------------------------------------------------