显示每种情况下的计数

Question

我想显示"ASO“，如下所示：

All count must in one row

但是我设法让display "ASO“如下：

It become 2 row

我的查询如下，以显示结果：

SELECT a.ID_Company AS [COMPANY CODE],sc.Company_Name as [COMPANY NAME],
CASE WHEN a.DeptProject_Type = 'D' THEN a.ID_DeptProject END AS [DEPARTMENT],
CASE WHEN a.DeptProject_Type = 'P'THEN a.ID_DeptProject END AS [PROJECT],
CASE WHEN a.PR_Status=7THEN COUNT(a.PR_Status) END AS DRAFT,
CASE WHEN a.PR_Status=8THEN COUNT(a.PR_Status) END AS SUBMITTED,
CASE WHEN a.PR_Status=9THEN COUNT(a.PR_Status) END AS REVIEWED,
CASE WHEN a.PR_Status=10THEN COUNT(a.PR_Status) END AS CANCELLED,
CASE WHEN a.PR_Status=11THEN COUNT(a.PR_Status) END AS REJECTED,
CASE WHEN a.PR_Status=12THEN COUNT(a.PR_Status) END AS APPROVED,
CASE WHEN a.PR_Status=13THEN COUNT(a.PR_Status) END AS ENDORSED,
CASE WHEN a.PR_Status=14THEN COUNT(a.PR_Status) END AS ASSIGNED,
CASE WHEN a.PR_Status=15THEN COUNT(a.PR_Status) END AS [CLOSED],
CASE WHEN a.PR_Status=23THEN COUNT(a.PR_Status) END AS [RE-APPROVAL]
from PR_MASTER a
left join SETUP_COMPANY sc on a.ID_Company=sc.ID_Company
group by a.ID_Company,sc.Company_Name,a.ID_DeptProject,a.DeptProject_Type,a.PR_Status
order by a.ID_Company,a.PR_Status

Answer 1

最后，我得到了答案。感谢我的辛勤工作和对寻找答案的执着。叹息

    SELECT DISTINCT a.ID_Company AS [COMPANY CODE],sc.Company_Name AS [COMPANY NAME],
CASE WHEN a.DeptProject_Type = 'D' THEN a.ID_DeptProject END AS [DEPARTMENT],
CASE WHEN a.DeptProject_Type = 'P'THEN a.ID_DeptProject END AS [PROJECT],
CASE WHEN draft.Status IS NULL THEN 0 ELSE draft.Status END AS DRAFT,
CASE WHEN submitted.Status IS NULL THEN 0 ELSE submitted.Status  END AS SUBMITTED,
CASE WHEN reviewed.Status IS NULL THEN 0 ELSE reviewed.Status  END AS REVIEWED,
CASE WHEN cancelled.Status IS NULL THEN 0 ELSE cancelled.Status  END AS CANCELLED,
CASE WHEN rejected.Status IS NULL THEN 0 ELSE rejected.Status  END AS REJECTED,
CASE WHEN approved.Status IS NULL THEN 0 ELSE approved.Status  END AS APPROVED,
CASE WHEN endorsed.Status IS NULL THEN 0 ELSE endorsed.Status  END AS ENDORSED,
CASE WHEN ASsigned.Status IS NULL THEN 0 ELSE ASsigned.Status  END AS ASSIGNED,
CASE WHEN closed.Status IS NULL THEN 0 ELSE closed.Status  END AS [CLOSED],
CASE WHEN reapproval.Status IS NULL THEN 0 ELSE reapproval.Status  END AS [RE-APPROVAL]
from PR_MASTER a
left join SETUP_COMPANY sc ON a.ID_Company=sc.ID_Company
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=7
            GROUP BY ID_Company,ID_DeptProject) AS draft ON a.ID_Company=draft.ID_Company and a.ID_DeptProject=draft.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=8
            GROUP BY ID_Company,ID_DeptProject) AS submitted ON a.ID_Company=submitted.ID_Company and a.ID_DeptProject=submitted.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=9
            GROUP BY ID_Company,ID_DeptProject) AS reviewed ON a.ID_Company=reviewed.ID_Company and a.ID_DeptProject=reviewed.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=10
            GROUP BY ID_Company,ID_DeptProject) AS cancelled ON a.ID_Company=cancelled.ID_Company and a.ID_DeptProject=cancelled.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=11
            GROUP BY ID_Company,ID_DeptProject) AS rejected ON a.ID_Company=rejected.ID_Company and a.ID_DeptProject=rejected.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=12
            GROUP BY ID_Company,ID_DeptProject) AS approved ON a.ID_Company=approved.ID_Company and a.ID_DeptProject=approved.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=13
            GROUP BY ID_Company,ID_DeptProject) AS endorsed ON a.ID_Company=endorsed.ID_Company and a.ID_DeptProject=endorsed.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=14
            GROUP BY ID_Company,ID_DeptProject) AS ASsigned ON a.ID_Company=ASsigned.ID_Company and a.ID_DeptProject=ASsigned.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=15
            GROUP BY ID_Company,ID_DeptProject) AS closed ON a.ID_Company=closed.ID_Company and a.ID_DeptProject=closed.ID_DeptProject
left join (SELECT ID_Company,ID_DeptProject,COUNT(PR_Status) AS Status FROM PR_MASTER
            WHERE PR_Status=23
            GROUP BY ID_Company,ID_DeptProject) AS reapproval ON a.ID_Company=reapproval.ID_Company and a.ID_DeptProject=reapproval.ID_DeptProject
WHERE a.ID_Company IS NOT NULL      
ORDER BY a.ID_Company