CODEWITHSUNDEEP
sqlsql-servert-sqlstring-aggregation
skrantz
skrantz

Reputation: 39

Is it possible to have Multiple String_Agg Columns with Conditions

I have the following table of Event Registrations. In the table, Muti-day events are recorded as an individual record for each registered date. In addition, if a date is full, the date can still be registered for, as a Waitlisted Date. I am trying to create a single record from the record in the table below where the dates are string aggregated. The table shown below, as well as several related tables are more complex than this, but I simplified for the sake of an example.

EventAssignments Table

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

EventDates Table

+--------+-------------------------+---------+------------+
| 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 |
+--------+-------------------------+---------+------------+

The desired query results should look like this:

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

I was thinking that I needed some logic, so I tried to implement the use of CASE, as show below. The results however still show as two records. the first record for regular dates, and the second for wait listed dates

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

Upvotes: 0

Views: 4396

Answers (2)

Justin
Justin

Reputation: 9724

Try this Query:

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

Result:

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

Upvotes: 1

FXD
FXD

Reputation: 2060

My fault for missing the consecutive <br /> in the result.

Can you try this one, using recursive CTE?
It may require the concat operator to be replaced as well as the ISNULL but I wasn't perfectly sure about your DBMS...

WITH OrderedDates AS (
SELECT a.UserID, a.EventID, a.AssignID, a.WaitListed, CONVERT(varchar, d.EventDate, 101)                AS strEventDate,
    ROW_NUMBER() OVER (Partition BY a.UserID, a.EventID, a.AssignID, a.WaitListed ORDER BY d.EventDate) AS EventDateOrder,
    COUNT(*)     OVER (Partition BY a.UserID, a.EventID, a.AssignID, a.WaitListed)                      AS EventDateCount
FROM EventAssignments AS a 
INNER JOIN EventDates AS d ON a.DateID = d.DateID
), ConcatDates AS (
    SELECT UserID, EventID, AssignID, WaitListed, EventDateOrder, EventDateCount, strEventDate
    FROM OrderedDates
    WHERE EventDateOrder = 1
    UNION ALL
    SELECT OD.UserID, OD.EventID, OD.AssignID, OD.WaitListed, OD.EventDateOrder, OD.EventDateCount, CD.strEventDate + '<br />' + OD.strEventDate
    FROM OrderedDates OD
    JOIN ConcatDates CD on OD.UserID = CD.UserID AND OD.EventID = CD.EventID AND OD.AssignID = CD.AssignID AND OD.WaitListed = CD.WaitListed AND OD.EventDateOrder = CD.EventDateOrder + 1
)
SELECT ISNULL(CD1.UserID, CD2.UserID), ISNULL(CD1.EventID, CD2.EventID), ISNULL(CD1.AssignID, CD2.AssignID), CD1.strEventDate, CD2.strEventDate
FROM            (SELECT * FROM ConcatDates WHERE EventDateCOunt = EventDateOrder AND WaitListed = 'false') CD1
FULL OUTER JOIN (SELECT * FROM ConcatDates WHERE EventDateCOunt = EventDateOrder AND WaitListed = 'true' ) CD2 ON CD1.UserID = CD2.UserID AND CD1.EventID = CD2.EventID AND CD1.AssignID = CD2.AssignID

Upvotes: 0

Related Questions