尝试 SUM 与 CASE ```
create table test(id INT, dept VARCHAR(20), location VARCHAR(100))
insert into test values
(1,'eee','chennai'),
(2,'ece','chennai'),
(3,'eee','chennai'),
(4,'ece','chennai'),
(5,'eee','cbe'),
(6,'ece','trichy'),
(7,'mech','madurai')

SELECT
location AS city,
SUM(CASE WHEN dept = 'eee' THEN 1 ELSE 0 END) eee,
SUM(CASE WHEN dept = 'ece' THEN 1 ELSE 0 END) ece,
SUM(CASE WHEN dept = 'mech' THEN 1 ELSE 0 END) mech
FROM test
GROUP BY location

输出

city eee ece mech
cbe 1 0 0
chennai 2 2 0
madurai 0 0 1
trichy 0 1 0

select location as city,
       sum(dept = 'eee') as eee,
       sum(dept = 'ece') as ece,
       sum(dept = 'mech') as mech
from your_table
group by location

CREATE TABLE #TABLE1
    ([ID] INT, [DEPT] VARCHAR(4), [LOCATION] VARCHAR(7))
;
INSERT INTO #TABLE1
    ([ID], [DEPT], [LOCATION])
VALUES
    (1, 'EEE', 'CHENNAI'),
    (2, 'ECE', 'CHENNAI'),
    (3, 'EEE', 'CHENNAI'),
    (4, 'ECE', 'CHENNAI'),
    (5, 'EEE', 'CBE'),
    (6, 'ECE', 'TRICHY'),
    (7, 'MECH', 'MADURAI')
;
SELECT [LOCATION], COUNT(CASE WHEN DEPT = 'EEE' THEN [DEPT] END ) EEE ,
COUNT(CASE WHEN DEPT = 'ECE' THEN DEPT END )  ECE
,COUNT(CASE WHEN DEPT = 'MECH' THEN DEPT END ) MECH FROM #TABLE1
GROUP BY [LOCATION]

输出

LOCATION    EEE ECE MECH
CBE          1   0   0
CHENNAI      2   2   0
MADURAI      0   0   1
TRICHY       0   1   0