时间戳

首页 > 解决方案 > 是否可以有多个带有条件的 String_Agg 列

sql - 是否可以有多个带有条件的 String_Agg 列

问题描述

我有下表的事件注册。在表中，多日事件被记录为每个注册日期的单独记录。此外，如果某个日期已满，该日期仍可注册为候补日期。我正在尝试从下表中的记录创建单个记录，其中日期是字符串聚合的。如下所示的表格，以及几个相关的表格比这更复杂，但我为了示例而简化了。

事件分配表

+--------+---------+----------+--------+------------+
| UserID | EventID | AssignID | DateID | WaitListed |
+--------+---------+----------+--------+------------+
|      1 |      19 |       14 |     67 |          0 |
|      1 |      19 |       14 |     68 |          0 |
|      1 |      19 |       14 |     69 |          1 |
+--------+---------+----------+--------+------------+

事件日期表

+--------+-------------------------+---------+------------+
| DateID |        EventDate        | EventID | DateTypeID |
+--------+-------------------------+---------+------------+
|     65 | 2019-03-20 00:00:00.000 |      19 |          1 |
|     66 | 2019-03-21 00:00:00.000 |      19 |          1 |
|     67 | 2019-03-22 00:00:00.000 |      19 |          2 |
|     68 | 2019-03-23 00:00:00.000 |      19 |          2 |
|     69 | 2019-03-24 00:00:00.000 |      19 |          2 |
|     70 | 2019-03-25 00:00:00.000 |      19 |          3 |
+--------+-------------------------+---------+------------+

所需的查询结果应如下所示：

+--------+---------+----------+----------------------------+---------------+
| UserID | EventID | AssignID |      RegisteredDates       | WaitListDates |
+--------+---------+----------+----------------------------+---------------+
|      1 |      19 |       14 | 03/22/2019<br />03/23/2019 | 03/24/2019    |
+--------+---------+----------+----------------------------+---------------+

我在想我需要一些逻辑，所以我尝试实现 CASE 的使用，如下所示。然而，结果仍然显示为两条记录。常规日期的第一条记录，等待列出日期的第二条记录

SELECT a.UserID, a.EventID, a.AssignID, 
(CASE WHEN a.WaitListed = 'false' 
    THEN STRING_AGG(CONVERT(varchar, d.EventDate, 101), '<br />') END) AS RegDates, 
(CASE WHEN a.WaitListed = 'true' 
    THEN STRING_AGG(CONVERT(varchar, d.EventDate, 101), '<br />') END) AS WaitListDates
FROM dbo.EventAssignments AS a 
INNER JOIN dbo.EventDates AS d ON a.DateID = d.DateID
GROUP BY a.UserID, a.EventID, a.WaitListed, a.AssignID

标签： sqlsql-servertsqlstring-aggregation

解决方案

试试这个查询：

SELECT a.UserID, 
       a.EventID, 
       a.AssignID, 
STRING_AGG(CASE WHEN a.WaitListed = 'false' 
    THEN CONVERT(varchar, d.EventDate, 101) END, '<br />') AS RegDates, 
STRING_AGG(CASE WHEN a.WaitListed = 'true' 
    THEN CONVERT(varchar, d.EventDate, 101) END, '<br />') AS WaitListDates
FROM dbo.EventAssignments AS a 
INNER JOIN dbo.EventDates AS d 
  ON a.DateID = d.DateID
GROUP BY a.UserID, a.EventID, a.AssignID

结果：

UserID  EventID AssignID    RegDates                    WaitListDates
1       19      14          03/22/2019<br />03/23/2019  03/24/2019

推荐阅读