Are people who understand relational databases and people who understand pivot tables disjoint sets?

I can look at someone’s finished pivot table and reproduce it from the data through other means, but any explanation of what a pivot table actually is and does reads like pure gibberish to me.

Probably. A pivot table is basically a way to turn on one of the dimensions of the sheet to make sense of the data. Like "show me all invoices, grouped by date and sum each group". It is effectively a query, in a way that makes sense for people working in spreadsheets.

The pivot tables don't require people to understand data normalization and software maintenance good practices. Outside of that, yeah, not really relational databases because those focus on having more than one table, but they do understand relations.

It is certainly much easier to shown than to tell about pivot tables. I tried to think of a good form of words to explain them but couldn't.

Check out the full version of Towards Scalable Dataframe Systems from VLDB 2020 [0]. They propose an algebra for dataframes and section 4.4's example succinctly describes the pivot operator.

[0] https://arxiv.org/abs/2001.00888