尝试使用 PIVOT -
Query 1:
DECLARE @temp TABLE (Type1 CHAR(1), Type2 CHAR(2))
INSERT INTO @temp (Type1, Type2)
VALUES
('A', 'T1'),('A', 'T2'),
('A', 'T1'),('A', 'T1'),
('A', 'T2'),('A', 'T3'),
('B', 'T3'),('B', 'T2'),
('B', 'T3'),('B', 'T3')
SELECT *
FROM @temp
PIVOT
(
COUNT(Type2) FOR Type2 IN (T1, T2, T3)
) p
Query 2:
SELECT
Type1
, T1 = COUNT(CASE WHEN Type2 = 'T1' THEN 1 END)
, T2 = COUNT(CASE WHEN Type2 = 'T2' THEN 1 END)
, T3 = COUNT(CASE WHEN Type2 = 'T3' THEN 1 END)
FROM @temp
GROUP BY Type1
Output:
Type1 T1 T2 T3
----- ----------- ----------- -----------
A 3 2 1
B 0 1 3