sql-server - 涉及 Null 和空白与填充值的复杂 SQL Server Pivot

Question

我需要将“dep”列分成 2 个不同的列，一个被“记录”，另一个被“未记录”。“未记录”列将包含当天“dep”为空白或为空的条目的时间总和。“记录”列需要显示“dep”列不为空或空白的那一天的时间总和。

到目前为止，这就是我所拥有的

 SELECT Cast(Start_Time AS DATE) AS Date, dep, 
 sum(time) as "Total Time"
 FROM A6K_Events
 Group By Cast(Start_Time AS DATE), dep

它产生了这个

 +------------+--------------+------------+
 | Date       | Dep          | Total Time |
 +------------+--------------+------------+
 | 2018-06-29 | Null         | 3544       |
 +------------+--------------+------------+
 | 2018-06-29 | Other        | 268        |
 +------------+--------------+------------+
 | 2018-06-29 | Training     | 471        |
 +------------+--------------+------------+
 | 2018-06-29 | Change Point | 371        |
 +------------+--------------+------------+
 | 2018-06-28 | Null         | 4519       |
 +------------+--------------+------------+
 | 2018-06-28 | Training     | 1324       |
 +------------+--------------+------------+
 | 2018-06-28 |              | 50         |
 +------------+--------------+------------+

这就是我想要的最终结果

+------------+----------+------------+
| Date       | Recorded | Unrecorded |
+------------+----------+------------+
| 2018-06-29 | 1110     | 3544       |
+------------+----------+------------+
| 2018-06-28 | 1324     | 4569       |
+------------+----------+------------+

任何建议或帮助将不胜感激。我无法弄清楚如何将空值和空白值旋转并过滤到一列中，而其他填充值到另一列中。

谢谢你。

标签： sql-servernullpivotgroupingblank-line

Answer 1

Pivot 不是必需的，我们可以使用 CASE 语句获取数据

 SELECT start_time as Date, sum(case when dep is not NULL or dep <> '' then time end) as "Recorded", sum(case when dep is NULL or dep = '' then time end) as "Unrecorded"
 FROM A6K_Events
 Group By start_time order by 1 desc