我推荐条件聚合：

select customer_num,
       sum(case when item_type = 'dress' then 1 else 0 end) as dress,
       sum(case when item_type = 'pants' then 1 else 0 end) as pants,
       sum(case when item_type = 'undies' then 1 else 0 end) as undies
from t
group by customer_num;

您可以简单地枢轴表如下：

SELECT * FROM
(
SELECT customer_num,item from table
)
PIVOT
(
COUNT(item)
FOR item in ('dress','pants','undies')
)
ORDER BY customer_num;

使用子查询并在customer_num上连接

SELECT  
    customer_num,dress, pant, ...
FROM
    (SELECT 
         customer_num, COUNT(*) AS dress 
     FROM 
         table 
     WHERE 
         item_type = 'dress' 
     GROUP BY 
         customer_num) r1
JOIN 
    (SELECT 
         customer_num, COUNT(*) AS pant 
     FROM 
         table  
     WHERE 
         item_type = 'pan'  
     GROUP BY 
         customer_num) r2 USING (customer_num)
JOIN ...