Outer Apply combine 2 rows into 1 row and 2 columns

Question

I am trying to write a query that combines multiple Outer Apply join results into single rows of data. I have played around with some CTEs but I just cant wrap my head around the solution.

I would like to have the multiple join results display as 1 row with the 2nd join result displayed in a 2nd column

SELECT 
     E.[EventId]
    ,S_ID.[EventType]
    ,null as [RootCause2]

 FROM [AOE_Workflow].[dbo].[Event] E

    outer apply
    (
    select * from [AOE_Workflow].[dbo].[EventTypes] S
    WHERE E.EventID=S.EventID
    ) S

    outer apply
    (
    select * from [AOE_Workflow].[dbo].[EventType] S_ID
    WHERE S_ID.[EventTypeId]=S.[EventTypeId]
    ) S_ID

ORDER BY eventID desc

Answer 1

If I do not get this wrong, your outer applys can be specified more easily with simple LEFT JOINs.

The side-by-side output can be done with PIVOT. With the given query there is no implicit sort order. That means, that it will be random, which is first, which is second (and third/fourth). In my SQL you can easily control the sorting when you change the (SELECT NULL) to something appropriate.

SELECT p.*
FROM
(
    SELECT 
         E.[EventId]
        ,S_ID.[EventType]
        ,'EventType_' + CAST(ROW_NUMBER() OVER(PARTITION BY E.[EventId] ORDER BY(SELECT NULL)) AS VARCHAR(1)) AS ColumnName
    FROM [AOE_Workflow].[dbo].[Event] E
    LEFT JOIN [AOE_Workflow].[dbo].[EventTypes] S ON E.EventID=S.EventID
    LEFT JOIN [AOE_Workflow].[dbo].[EventType] S_ID ON S_ID.[EventTypeId]=S.[EventTypeId]
) AS tbl
PIVOT
(
    MIN(EventType) FOR ColumName IN(EventType_1,EventType_2,EventType_3,EventType_4)
) AS p