A SQL statement to count the number of identical names

SQL statement Query
The table structure is like this:
ID name Sex
1 Three men
2 Wang Shinan
3 Lili Girl
4 Three Men
5 Chao Liuan
6 Noble Male
7 Wang Shiju
8 Noble Women
9 Three Girls
How can I query the following results with an SQL statement

Names, number of men, numbers of women, including IDs.
Zhang 33 2 1 1,4,9
Wang 42 1 1 2,7
Lili 1 0 1 3
Noble 2 1 1 6,8
Zhao Liu 1 1 0 5
Copy Code code as follows:
SELECT * FROM (select DISTINCT name,count (ID) as human numbers,
SUM (case when sex= "man" then 1 else 0 end) as man,
SUM (case when sex= "female" then 1 else 0 end) as Woman
From Test GROUP by Name A
OUTER APPLY (
SELECT
[Contains id]= STUFF (replace (
(
SELECT ID from Test N
WHERE Name = A.name
For XML AUTO
), "<n id=" ",", ")," "/>", "" "), 1, 1," ")
) N