How ARRAY() can function as UNPIVOT and UNNEST as PIVOT?

I’ve come across this SQL transformation multiple times, and it’s an interesting two-way problem.

1️⃣ From columns to rows (ARRAY as UNPIVOT):
We start with separate timestamps for different lifecycle events. To analyze events dynamically, we reshape them into an ARRAY<STRUCT>—essentially converting columns into rows, similar to UNPIVOT.

2️⃣ From rows back to columns (UNNEST as PIVOT):
If we have an array of events, we may need to do the opposite — bringing individual event types back into separate columns, similar to PIVOT. We achieve this by UNNESTing the array and using conditional aggregation (aggregation function + CASE WHEN).

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.