sql-server - SQL - 如何组合行

我有下表显示了通话和出勤情况。我通过在“通话”和“出勤”表上使用 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

我想修改它看起来像下面的输出，这样无论何时在调用之后出现出席，它都应该在同一行。

表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

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

最佳答案

使用完全连接

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

关于sql-server - SQL - 如何组合行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55282607/