我有列的表格如下:
- Sr.no Subject No of class attended
- -------------------------------------
- 1 English 3
- 2 Maths 4
- 3 SocialScience 5
我希望这种格式的表格
- English Maths SocialScience
- ---------------------------------
- 3 4 5
我试过这个:
- Select case when subject ='Maths' then COUNT(No_of_Candidates) else null end as Maths
但有了这个,我得到这样的数据:
- English Maths SocialScience
- ---------------------------------
- 3
- 4
- 5
请帮帮我怎么解决这个问题..
解决方法
正如你所说,你不希望输出像这样:
- English Maths SocialScience
- ---------------------------------
- 3
- 4
- 5
您需要使用这样的子查询:
- SELECT English,Maths,SocialScience
- FROM (
- SELECT Subject,No_of_class_attended
- FROM mytable) up
- PIVOT
- (Sum([No_of_class_attended])
- for Subject in ([English],[Maths],[SocialScience])) p
输出:
- English Maths SocialScience
- ---------------------------------
- 3 4 5