问题描述
大家好，
我在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.
推荐答案
select [ClassName],[Male],[female],[House]
from
(select * from tblex)source pivot
(count(Gender) for Gender in ([Male],[female]))pt
order by ClassName
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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！