An SQL sentence

SQL statement Query

The table structure is as follows: ID name gender 1 Zhang San male 2 Wang 4 male 3 Lili female 4 Zhang San male 5 Zhao Liu male 6 Gao Jie male 7 Wang 4 female 8 Gao Jie female 9 Zhang San female how can I use an SQL statement to query the following result shows the number of men and women in the name. The number of men and women includes ID Zhang 3 3 2 1 1 1 4, 9 King 4 2 1 Lili 1 0 1 3 Gao Jie 2 1 6 6 8 Zhao Liu 1 1 0 5

Select * from (select distinct name, count (ID) as number of people,

Sum (case when sex = 'male' then 1 else 0 end) as men,

Sum (case when sex = 'female' then 1 else 0 end) as female count

From test group by name)

Outer apply (

Select

[Include id] = stuff (replace (

(

Select ID from Test N

Where name = A. Name

For XML auto

), '<N id = "', ','), '"/>', ''), 1, 1 ,'')

) N