这就是你需要的

SELECT
        cust_mob_no,  
        SUM(CASE WHEN (Status='unattended') THEN 1 ELSE 0 END) AS unattended,
        SUM(CASE WHEN (Status='closed' AND pagecount=1) THEN 1 ELSE 0 END) AS closed,
        SUM(CASE WHEN (Status='NotApplicable' AND pagecount=2) THEN 1 ELSE 0 END) AS NotApplicable,
        SUM(CASE WHEN (Status='Assigned' AND pagecount=3) THEN 1 ELSE 0 END) AS Assigned
    FROM 
        your_table
    GROUP BY 
        cust_mob_no

你可以这样使用select case

SELECT
    mobileNumber,  
    SUM(CASE WHEN (statusName='unattended') THEN 1 ELSE 0 END) AS unattended,
    SUM(CASE WHEN (statusName='closed' ) THEN 1 ELSE 0 END) AS closed,
    SUM(CASE WHEN (statusName='NotApplicable') THEN 1 ELSE 0 END) AS NotApplicable,
    SUM(CASE WHEN (statusName='Assigned') THEN 1 ELSE 0 END) AS Assigned
FROM 
    MyTable
GROUP BY 
    mobileNumber

dbfiddle示例代码
你可以利用pivot。示例查询

SELECT [unattended], [closed],[NotApplicable],[Assigned],[open] FROM   
(SELECT [mobileNumber],[status] FROM MyTable )Tab1  
PIVOT  
(  
COUNT([mobileNumber]) FOR status IN ([unattended], [closed],[NotApplicable], 
[Assigned],[open])) AS Tab2

db fiddle示例代码