Column and column conversion in SQL Server

Original table structure

Serial number	Gender	Department	Salary
1	Male	Department	800
2	Female	Department B	900
3	Male	Department	400
4	Female	Department d	1400
5	Male	Department e	1200
6	Male	Department F	500
7	Female	Department	300
8	Male	Department d	1000
9	Female	Department d	1230
10	Female	Department B	2000
11	Male	Department C	2000
12	Male	Department B	1200

Final display

Department name	Number of students	Male	Female	Less than 800 yuan	From 800 to 999	From RMB 1000 to RMB 1190	More than 1200 yuan
Department	3	2	1	2	1	0	0
Department B	3	1	2	0	1	0	2
Department c	1	1	0	0	0	0	1
Department d	3	1	2	0	0	1	2
Department e	1	1	0	0	0	0	1
Department f	1	1	0	1	0	0	0

SELECT Department name, COUNT (number) as number of people,
SUM (CASE gender WHEN 1 THEN 1 ELSE 0 END) as male,
SUM (CASE gender WHEN 2 THEN 1 ELSE 0 END) as female,
SUM (case sign (salary-800) WHEN-1 THEN 1 ELSE 0 END) as less than 800 yuan,
SUM (case sign (salary-800) * SIGN (salary-1000)/** // * use * to implement <and> Functions */
WHEN-1 THEN 1 ELSE 0 END) + (CASE salary
When 800 then 1 else 0 end) as from 800 to 999,/** // note that aliases cannot start with numbers */
Sum (case sign (salary-1000) * sign (salary-1200)
When-1 then 1 else 0 end) + (Case salary
When 1000 then 1 else 0 end) as from 1000 yuan to 1199 yuan,
Sum (case sign (salary-1200) when 1 then 1 else 0 end)
+ (Case salary when 1200 then 1 else 0 end) as more than 1200 yuan
From payroll
Group by department name