Query in statistical reports

The table data structure is as follows:

No class sex
01001 Grade 3 male
Third-grade female 01002
Third-grade female 01003
01004 Grade 4 male
01005 Grade 4 male
Fifth-grade female
01007 sixth-grade female

The following result set is displayed:

Grade male count
Grade 1 2 3
Grade 2 0 2
Fifth grade 0 1 1
Sixth grade 0 1 1 1
Total 3 4 7

 

 Select Class,Sum ( Case   When Sex =  '  Male  '   Then   1   Else   0   End  ),  Sum ( Case   When Sex=  '  Female  '   Then   1   Else   0   End  )  From A Group   By Class

 

This is very simple, but the trouble is that sex is not fixed, that is to say, there may also be a demon, and other unfixed gender fields.

So we need to use a loop, so we need to use a cursor. First we need to find all the sex fields. Then, the cursor is used to traverse the result set and concatenate strings cyclically.

 Declare  @ Sex   Varchar ( 50  )  Declare   @ SQL   Varchar ( 500  )  Set   @ SQL  =  '  Select class,  '  Declare MycursorCursor   For   Select Sex From Test Group   By  Sex  Open Mycursor Fetch   Next   From Mycursor Into   @ Sex  While ( @ Fetch_status =  0  )  Begin  If ( @ Sex  =  ''   Or   @ Sex  =  Null  )  Set   @ Sex  =  '  Not Configured '  Set   @ SQL  + =  '  Sum (case when sex =  '''  +  @ Sex  +  '''  Then 1 else 0 end)  '  +  @ Sex  +  '  , '  Fetch   Next   From Mycursor Into   @ Sex  End  Close Mycursor Deallocate  Mycursor  Select   @ SQL  =  Substring ( @ SQL , 1 , Len ( Ltrim ( Rtrim ( @ SQL ))) -  1  )  Set   @ SQL  + =  '  From test group by class  '  Exec ( @ SQL )