使用 SQL Server 中的透视根据请求状态获取优先级数据

Question

我必须在SQL Server 2012中运行查询，以根据请求状态从表中选择数据。

我正在使用查询，不包括获取当前一周数据的 where 条件。

无位置条件的查询

select * from (SELECT ReqStatus,[priority] 
FROM WEEKDATA
group by ReqStatus,[priority] ,Raisedon
)p
pivot (
    Count([priority])
    For [priority] IN(Critical,High,Medium,Low)
 )
as pvt

结果

|ReqStatus | Critical| High| Medium| Low 
|----------|---------|-----|-------|----
| Approved |    1    |  0  |   0   | 0
| Assigned |    2    |  0  |   0   | 0
| Closed   |    2    |  1  |   1   | 1
| Rejected |    0    |  1  |   0   | 0

当我在查询当前周数据时包含 where 条件时

使用位置条件进行查询

select * from (SELECT ReqStatus,[priority] 
FROM WEEKDATA
    where DATENAME(wk,Raisedon)=DATENAME(wk,GETUTCDATE())
    group by ReqStatus,[priority] ,Raisedon
    )p
    pivot (
        Count([priority])
        For [priority] IN(Critical,High,Medium,Low)
     )
    as pvt

实际结果

|ReqStatus | Critical| High| Medium| Low 
|----------|---------|-----|-------|----
| Assigned |    1    |  0  |   0   | 0
| Closed   |    0    |  0  |   1   | 0

我得到上述结果是因为本周我的表中只有两个状态"已分配"，"已关闭"。但我还需要列表中的其他请求状态，例如"已批准"、"已拒绝"，列中有 0 个值，即严重、高、中、低。

例如

预期成果

|ReqStatus | Critical| High| Medium| Low 
|----------|---------|-----|-------|----
| Approved |    0    |  0  |   0   | 0
| Assigned |    1    |  0  |   0   | 0
| Closed   |    0    |  0  |   1   | 0
| Rejected |    0    |  0  |   0   | 0

Answer 1

您可以将 CTE 与 UNION ALL 一起使用，以添加值为 0 的状态。

;WITH CTE_wd AS
(
    SELECT ReqStatus,[priority] 
    FROM WEEKDATA
    where DATENAME(wk,Raisedon)=DATENAME(wk,GETUTCDATE())
    group by ReqStatus,[priority] ,Raisedon
)   
select * from (SELECT ReqStatus,[priority] 
FROM CTE_wd
UNION ALL
SELECT DISTINCT w.ReqStatus , '' AS [priority]
FROM WEEKDATA AS w
LEFT JOIN CTE_wd AS wd ON wd.ReqStatus = w.ReqStatus
WHERE wd.ReqStatus IS NULL
)p
pivot (
    Count([priority])
    For [priority] IN(Critical,High,Medium,Low)
 )
as pvt