What's the best practise to write SQL to get data as follow ?

Answer 1

You can try something like this

SELECT  c.Status,
     SUM(CASE WHEN u.Gender = 0 THEN 1 ELSE 0 END) Male, --assuming 0 is male
     SUM(CASE WHEN u.Gender = 1 THEN 1 ELSE 0 END) Female --assuming 1 is female
FROm    Competitors c INNER JOIN
     UserInfomration u ON c.UserID = u.ID
GROUP BY c.Status