Multiple pivots in U-SQL to output multiple columns?

Is it possible to perform multiple pivots in U-SQL without doing a UNION? Something along the lines of:

SELECT Email
FROM @somedata
PIVOT (
     MIN(EventTimestamp) FOR EventType IN ("A" AS FirstATime, "B" AS FirstBTime)
),
PIVOT (
     MAX(EventTimestamp) FOR EventType IN ("A" AS LastATime, "B" AS LastBTime)
)
GROUP BY Email

The resulting columns should be:

Email, FirstATime, FirstBTime, LastATime, LastBTime

1 answer

  • answered 2018-02-14 19:16 Michael Rys

    You can compose PIVOT expressions. Please note that PIVOT is a rowset expression. Thus it will take a rowset on its left hand side argument.

    I think you would like to apply two different aggregations over the same data with the PIVOTs though. In that case, I think you will need to do it in two SELECTs and then do an OUTER UNIONon the Email to merge the rows.