如何对我的问题进行Sql查询 [英] How to make Sql query for my problem

问题描述

大家好，

我在asp.net中工作.我有一张桌子MyClass.在这里，我有三列.
列是类名，性别，房子.

记录以以下格式填写:
ClassName性别之家
abc公兔
abc公牛
abc母兔
kbc公兔
kbc母公牛
kbc公兔


我的要求是我想以以下格式显示记录.



类名，男性，女性，房屋
abc 1 1兔子
abc 1 0公牛
kbc 2 0兔子
kbc 0 1公牛

类名，男性，女性，房屋
将是输出中的列名.请帮助我，对此将进行什么查询.

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.

推荐答案

嗨..

请使用以下查询.

这对您有帮助

Hi..

Please use the below query.

It will help u

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

order by ClassName

试试这个.......................

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

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
--------------------------------------------------

这篇关于如何对我的问题进行Sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！