Forgive me if this seems like an arbitrary question as I am fairly new to SQL but have done as much research as I can.
We use SQL databases as a datasource in Tableau. The problem is that we're trying to replicate a function that Tableau offers, but inside of SQL itself. What Tableau is able to do is "union" tables directly on top of one another, even if the columns are not all equal or of the same type. It then simply places nulls inside cells that don't have corresponding values in any of the tables. Tableau then lets you 'pivot' selected columns from the newly unioned table. However, Tableau only lets you do this using Excel and similar files, but not SQL tables.
So my dilemma is I'm trying to discern whether there is a way (even if it's a complicated way) to achieve similar results in SQL itself, first.
So if we took the 3 example tables below, we'd want to union them in SQL like this:
And then we'd want to pivot the "Sales", "Refunds", "Complaints" and "Customers" columns into a single column, like this:
Again, I'm new to SQL but I can't imagine why this would be so difficult to achieve? But wherever I've researched this, people say it's almost impossible to do so?