sql-server - SQL - 如何合并行

Question

我有下表，其中显示了调用和出勤情况。我通过在“调用”和“出勤”表上使用 union all 来获得此结果，然后在 ID 上使用行号并按日期排序。

表1:

     Type    | ID | Call/AttendanceDate | RowNum 
 ------------|----|---------------------|-------- 
  Attendance | 12 | 2018-09-16 10:11:00 |     82 
  Call       | 12 | 2018-09-18 14:11:47 |     83 
  Call       | 12 | 2018-10-02 17:26:13 |     84 
  Call       | 12 | 2018-10-05 14:58:31 |     85 
  Attendance | 12 | 2018-10-13 01:41:00 |     86 
  Call       | 12 | 2018-10-13 02:39:12 |     87 
  Call       | 12 | 2018-10-13 04:31:22 |     88 
  Attendance | 12 | 2018-10-13 14:29:00 |     89 
  Call       | 12 | 2018-10-13 14:59:19 |     90 
  Attendance | 12 | 2018-10-15 15:50:00 |     91 

我为此使用的代码是:

WITH CTE1 AS
(
SELECT 'Call' as [Type], ID, CallDate AS Date1 
FROM CallsTable     

UNION ALL

SELECT 'Attendance' as [Type], ID, AttendanceDate AS Date2
FROM AttendanceTable] 
)

,CTE2 AS
(
SELECT [Type], Date1, ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date1 ASC) AS RowNum
FROM CTE1
)

--------------------------------OUTPUT--------------------------------

SELECT a.[Type], a.ID, a.Date1, a.RowNum
FROM CTE2 a
JOIN CTE2 b
    ON a.ID= b.ID
    AND a.RowNum = b.RowNum + 1
WHERE a.ID = '12'
ORDER BY ID, RowNum

我想将其修改为如下所示的输出，以便每当电话 session 后出现出席情况时，它都应该位于同一行。

表2:

  Type | ID |     CallDate     | RowNum |    Type    |  AttendanceDate  | RowNum 
 ------|----|------------------|--------|------------|------------------|-------- 
  NULL | 12 | NULL             | NULL   | Attendance | 16/09/2018 10:11 | 82     
  Call | 12 | 18/09/2018 14:11 | 83     | NULL       | NULL             | NULL   
  Call | 12 | 02/10/2018 17:26 | 84     | NULL       | NULL             | NULL   
  Call | 12 | 05/10/2018 14:58 | 85     | Attendance | 13/10/2018 01:41 | 86     
  Call | 12 | 13/10/2018 02:39 | 87     | NULL       | NULL             | NULL   
  Call | 12 | 13/10/2018 04:31 | 88     | Attendance | 13/10/2018 14:29 | 89     
  Call | 12 | 13/10/2018 14:59 | 90     | Attendance | 15/10/2018 15:50 | 91  

这可能吗？我可以使用什么代码？

Answer 1

使用完整连接

SELECT 
*
FROM 
(SELECT * FROM CTE2 WHERE Type = 'CALL') A
FULL JOIN
(SELECT * FROM CTE2 WHERE Type = 'ATTENDANCE') B
ON A.ID = B.ID AND A.RowNum = B.RowNum - 1