Extending a Pivot

Question

I have a Documents table and an Events table.

• Documents table has ID and a bunch of other fields not relevant to this question.
• Events table has DocID, EventType, EventDate, and UserID.

A document may have zero or more Events of any of these EventTypes:

• 0 = Created
• 1 = Modified
• 2 = Submitted
• 3 = Approved

 DocID  | EventType | EventDate | UserID
-----------------------------------------------
    1   |    0      | 1-2-2017  |  123  
    1   |    1      | 1-3-2017  |  456
    1   |    1      | 1-4-2017  |  489
    1   |    2      | 1-5-2017  |  357
    2   |    0      | 1-6-2017  |  951
    2   |    1      | 1-7-2017  |  654
    2   |    2      | 1-8-2017  |  654
    2   |    3      | 1-9-2017  |  357

Pivoting the Events table is easy enough:

SELECT DocID, [0] AS CreatedDate, [1] AS ModifiedDate,
              [2] AS SubmittedDate, [3] AS ApprovedDate
FROM (SELECT DocID, EventType, EventDate FROM Events
WHERE DocID IS NOT NULL AND EventDate IS NOT NULL) AS DocEvents
PIVOT (MAX(EventDate) FOR EventType IN ([0], [1], [2], [3]))
AS DocEventsPivot

For my purposes, the most recent event of a given type is wanted, thus the MAX aggregate:

DocID | CreatedDate | ModifiedDate | SubmittedDate | ApprovedDate
-----------------------------------------------------------------
  1   |   1-2-2017  |    1-4-2017  |    1-5-2017   |     NULL
  2   |   1-6-2017  |    1-7-2017  |    1-8-2017   |   1-9-2017

How can I get the UserID translated to CreatedBy, ModifiedBy, SubmittedBy, and ApprovedBy to correspond to the dates of the appropriate EventType?

I will not know the possible values of UserID in advance.

Desired Output:

DocID | CreatedDate | ModifiedDate | SubmittedDate | ApprovedDate | CreatedBy | ModifiedBy | SubmittedBy | ApprovedBy
---------------------------------------------------------------------------------------------------------------------
  1   |   1-2-2017  |    1-4-2017  |    1-5-2017   |     NULL     |    123    |    489     |     357     |    NULL
  2   |   1-6-2017  |    1-7-2017  |    1-8-2017   |   1-9-2017   |    951    |    654     |     654     |    657

Answer 1

Rather than using PIVOT another solution is using OUTER APPLY.

CREATE TABLE #Documents (ID int)
CREATE TABLE #Events (DocID int, EventType int, EventDate date, UserID int)

INSERT INTO #Documents VALUES
(1),
(2)

INSERT INTO #Events VALUES
(1, 0, '1-2-2017', 123),  
(1, 1, '1-3-2017', 456),
(1, 1, '1-4-2017', 489),
(1, 2, '1-5-2017', 357),
(2, 0, '1-6-2017', 951),
(2, 1, '1-7-2017', 654),
(2, 2, '1-8-2017', 654),
(2, 3, '1-9-2017', 357)

SELECT
    DOC.ID AS 'DocID',
    CRT.EventDate AS 'CreatedDate',
    MFY.EventDate AS 'ModifiedDate',
    SUB.EventDate AS 'SubmittedDate',
    APR.EventDate AS 'ApprovedDate',
    CRT.UserID AS 'CreatedBy',
    MFY.UserID AS 'ModifiedBy',
    SUB.UserID AS 'SubmittedBy',
    APR.UserID AS 'ApprovedBy'
FROM 
    #Documents AS DOC
    OUTER APPLY (SELECT TOP 1 EventDate, UserID FROM #Events WHERE DocID = DOC.ID AND EventType = 0 ORDER BY EventDate DESC) AS CRT
    OUTER APPLY (SELECT TOP 1 EventDate, UserID FROM #Events WHERE DocID = DOC.ID AND EventType = 1 ORDER BY EventDate DESC) AS MFY
    OUTER APPLY (SELECT TOP 1 EventDate, UserID FROM #Events WHERE DocID = DOC.ID AND EventType = 2 ORDER BY EventDate DESC) AS SUB
    OUTER APPLY (SELECT TOP 1 EventDate, UserID FROM #Events WHERE DocID = DOC.ID AND EventType = 3 ORDER BY EventDate DESC) AS APR

DROP TABLE #Documents
DROP TABLE #Events

Answer 2

Try the following

CREATE TABLE #Events (DocID int, EventType int, EventDate date, UserID int)

INSERT INTO #Events VALUES
(1, 0, '1-2-2017', 123),  
(1, 1, '1-3-2017', 456),
(1, 1, '1-4-2017', 489),
(1, 2, '1-5-2017', 357),
(1, 2, '1-4-2017', 666),
(2, 0, '1-6-2017', 951),
(2, 1, '1-7-2017', 654),
(2, 2, '1-8-2017', 654),
(2, 3, '1-9-2017', 357)

SELECT
  DocID,
  MAX(CASE WHEN EventType=0 THEN EventDate END) [CreatedDate],
  MAX(CASE WHEN EventType=1 THEN EventDate END) [ModifiedDate],
  MAX(CASE WHEN EventType=2 THEN EventDate END) [SubmittedDate],
  MAX(CASE WHEN EventType=3 THEN EventDate END) [ApprovedDate],
  MAX(CASE WHEN EventType=0 THEN LastUserID END) [CreatedBy],
  MAX(CASE WHEN EventType=1 THEN LastUserID END) [ModifiedBy],
  MAX(CASE WHEN EventType=2 THEN LastUserID END) [SubmittedBy],
  MAX(CASE WHEN EventType=3 THEN LastUserID END) [ApprovedBy]
FROM
  (
    SELECT
      DocID,
      EventDate,
      EventType,
      LAST_VALUE(UserID)OVER(
                              PARTITION BY DocID,EventType
                              ORDER BY EventDate
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            ) LastUserID
    FROM #Events
  ) q
GROUP BY DocID


DROP TABLE #Events

Or you can use IFF instead CASE. I prefer use CASE because it isn't block ELSE

SELECT
  DocID,
  MAX(IIF(EventType=0,EventDate,NULL)) [CreatedDate],
  MAX(IIF(EventType=1,EventDate,NULL)) [ModifiedDate],
  MAX(IIF(EventType=2,EventDate,NULL)) [SubmittedDate],
  MAX(IIF(EventType=3,EventDate,NULL)) [ApprovedDate],
  MAX(IIF(EventType=0,LastUserID,NULL)) [CreatedBy],
  MAX(IIF(EventType=1,LastUserID,NULL)) [ModifiedBy],
  MAX(IIF(EventType=2,LastUserID,NULL)) [SubmittedBy],
  MAX(IIF(EventType=3,LastUserID,NULL)) [ApprovedBy]
FROM
  (
    SELECT
      DocID,
      EventDate,
      EventType,
      LAST_VALUE(UserID)OVER(
                              PARTITION BY DocID,EventType
                              ORDER BY EventDate
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            ) LastUserID
    FROM #Events
  ) q
GROUP BY DocID