时间戳

首页 > 解决方案 > SQL 将多行合二为一

sql - SQL 将多行合二为一

问题描述

我想将多行合并为一行，并且只保留值不为 NULL 的值

这是我想要实现的目标：

我想从这个

+----+-----------------+-----------------+-----------------+--------------------+
| ID | 1stNofification | 2ndNotification | 3rdNotification | NotificationNumber |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | 01.01.2019      | NULL            | NULL            | 1                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | NULL            | 02.02.2019      | NULL            | 2                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | NULL            | NULL            | 03.03.2019      | 3                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | 06.01.2019      | NULL            | NULL            | 1                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | NULL            | 09.02.2019      | NULL            | 2                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | NULL            | NULL            | 11.03.2019      | 3                  |
+----+-----------------+-----------------+-----------------+--------------------+

对此：

+----+-----------------+-----------------+-----------------+
| ID | 1stNofification | 2ndNotification | 3rdNotification |
+----+-----------------+-----------------+-----------------+
| 1  | 01.01.2019      | 02.02.2019      | 03.03.2019      |
+----+-----------------+-----------------+-----------------+
| 2  | 06.01.2019      | 09.02.2019      | 11.03.2019      |
+----+-----------------+-----------------+-----------------+

我试过类似的东西：

SELECT 
ID, 
MAX(CASE WHEN a.NotificationNumber = 1 THEN 1stNotification END)1stNotification,
MAX(CASE WHEN a.NotificationNumber = 2 THEN 2ndNotification END)2ndNotification, 
MAX(CASE WHEN a.NotificationNumber = 3 THEN 3rdNotification END)3rdNotification

FROM Notifications

GROUP BY ID

但不幸的是，这并没有给我预期的结果。

如果有人可以帮助我，我将不胜感激:)

标签： sqlsql-servergroup-by

解决方案

你只需要使用 max 没有任何情况

SELECT 
ID, 
MAX(1stNotification) AS 1stNotification,
MAX(2ndNotification) AS 2ndNotification, 
MAX(3rdNotification) AS 3rdNotification

FROM Notifications

GROUP BY  ID

推荐阅读