如何将教学大纲和性别方面的学生分开。 [英] How to separate student in Syllabus and Gender wise.

问题描述

Class1        Syllabus	Sex
1st Standard	ICSE	Boy
1st Standard	ICSE	Girl
1st Standard	CBSE	Girl
9th Standard	CBSE	Girl
1st Standard	CBSE	Boy
1st Standard	CBSE	Boy
9th Standard	CBSE	Boy
9th Standard	ICSE	Boy
9th Standard	CBSE	Boy
2nd Standard	CBSE	Boy
2nd Standard	CBSE	Boy
2nd Standard	ICSE	Boy
2nd Standard	ICSE	Boy
2nd Standard	ICSE	Boy
2nd Standard	STATE	Boy
2nd Standard	STATE	Boy
2nd Standard	STATE	Boy
2nd Standard	STATE	Boy
4th Standard	STATE	Girl
4th Standard	STATE	Girl
4th Standard	CBSE	Girl
4th Standard	CBSE	Girl
2nd Standard	CBSE	Girl
4th Standard	CBSE	Girl
2nd Standard	CBSE	Girl
1st Standard	CBSE	Boy
1st Standard	CBSE	Boy
Lkg	        CBSE	Girl
Lkg	        STATE	Girl
1st Standard	ICSE	Girl
Lkg	        ICSE	Girl
1st Standard	ICSE	Girl
Nursery	        STATE	Girl
Lkg	        ICSE	Boy
Ukg	        ICSE	Boy
Ukg	        STATE	Boy
Ukg	        STATE	Boy
Ukg	        STATE	Boy 

这是我存储在一张桌子里的数据。那个表名TblStudentRegistrationForm ok。但我希望在下面显示这样的。



例如幼儿班男孩是20名成员在ICSE中意味着它的表演在20和女孩是10是有它的节目10在ICSE Girl.like这个CBSE和STATE也是如何munch女孩和男孩在CBSE和STATE下面显示。

This is my data stored in one table. that table name TblStudentRegistrationForm ok. but i want like this in below showing.

for example nursery class boy are 20 member is there in ICSE means its show in 20 and girls is 10 is there its show 10 in ICSE Girl.like this CBSE and STATE also how munch Girls and boy is there that shows in below CBSE and STATE .

Class1        ICSE             CBSE	        STATE		
              Boy    Girl      Boy     Girl     Boy    Girl
 Nursery      20      10        5       15      45      60 

1stStandard   40       30       60       90      10      5

2ndStandard   60       25       50        50     35      45

[edit]已添加代码块 - OriginalGriff [/ edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

尝试：

Try:

SELECT Class1, 
       SUM(CASE WHEN Sex='Boy' AND syllabus='ICSE' THEN 1 ELSE 0 END) AS [ICSE Boy],
       SUM(CASE WHEN Sex='Girl' AND syllabus='ICSE' THEN 1 ELSE 0 END) AS [ICSE Girl],
       SUM(CASE WHEN Sex='Boy' AND syllabus='CBSE' THEN 1 ELSE 0 END) AS [CBSE Boy],
       SUM(CASE WHEN Sex='Girl' AND syllabus='CBSE' THEN 1 ELSE 0 END) AS [CBSE Girl],
       SUM(CASE WHEN Sex='Boy' AND syllabus='STATE' THEN 1 ELSE 0 END) AS [STATE Boy],
       SUM(CASE WHEN Sex='Girl' AND syllabus='STATE' THEN 1 ELSE 0 END) AS [STATE Girl]
FROM TblStudentRegistrationForm
GROUP BY Class1 

We can get the same result with union also but it is lengthy one

SELECT DISTINCT A.Class1, 
SUM([ICSE Boy]) AS  [ICSE Boy] ,SUM([ICSE Girl])AS [ICSE Girl],
SUM([CBSE Boy]) AS  [CBSE Boy] ,SUM([CBSE Girl])AS [CBSE Girl],
SUM([STATE Boy])AS  [STATE Boy] ,SUM([STATE Girl])AS [STATE Girl]
FROM (
select DISTINCT Class1,
SUM(Case when Syllabus='ICSE' then 1 else 0 end) as [ICSE Boy] ,0 AS [ICSE Girl],
SUM(Case when Syllabus='CBSE' then 1 else 0 end) as [CBSE Boy] ,0 AS [CBSE Girl],
SUM(Case when Syllabus='STATE' then 1 else 0 end) as [STATE Boy] ,0 AS [STATE Girl]
from TblStudentRegistration
WHERE Sex='Boy'
group by Class1
union 
select DISTINCT Class1,
0 as [ICSE Boy] ,SUM(Case when Syllabus='ICSE' then 1 else 0 end) AS [ICSE Girl],
0 as [CBSE Boy] ,SUM(Case when Syllabus='CBSE' then 1 else 0 end) AS [CBSE Girl],
0 as [STATE Boy] ,SUM(Case when Syllabus='STATE' then 1 else 0 end) AS [STATE Girl]
from TblStudentRegistration
WHERE Sex !='Boy'
group by Class1
) AS A
group by A.Class1

这篇关于如何将教学大纲和性别方面的学生分开。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！